How to calculate a person’s age in SQL Server?

Question

Suppose the table Pessoa and the countryside DataNascimento.

In an SQL query, what is the best way to calculate a person’s age in integer format in the T-SQL language of SQL Server?

Answer 1

The fairest way I know is:

SELECT FLOOR(DATEDIFF(DAY, DataNascimento, GETDATE()) / 365.25)

---

This way is more accurate than using one-year hours (as @rodrigorgs suggested) because the time of day the query is executed is not considered. In some cases, if a person has a birthday the day after today, the query considering the time will inform as if the person had already had a birthday, when in fact they did not (unless it runs exactly at 00:00).

Example:

DECLARE @DataNascimento DATETIME = '1991-12-12'
DECLARE @Hoje DATETIME = '2013-12-11 17:00:00' -- exemplo de horário da função GETDATE()
                           -- funciona corretamente se alterado acima para hora 00:00:00

SELECT FLOOR(DATEDIFF(DAY, @DataNascimento, @Hoje) / 365.25) -- retorna 21, idade correta
SELECT DATEDIFF(HOUR, @DataNascimento, @Hoje) / 8766 -- retorna 22

Answer 2

I prefer to go to the more extensive and safe side and use a function with traditional logic of age calculation, to have no problems with rounding and formulas:

SELECT YEAR(dHoje)-YEAR(dNasc)-IIF(MONTH(dHoje)*32+DAY(dHoje)<MONTH(dNasc)*32+DAY(dNasc),1,0)

You can exchange dHoje for GETDATE() (CURDATE() in Mysql) or even CURRENT_TIMESTAMP when applying to the database. I made the example with variables so you can easily test using DECLARE (however, remember to put @ before variables).

Notes:

• I used 32 as multiplier of the month only as good practice (power from 2), but being from 31 up is ok;

• As we do not consider time in the calculation of age, the IIF is adjusted so that the number of years of age "rises" exactly on the day of the person’s birthday;

• Remember that in this case we are considering the birthday of those born on February 29 as March 1 in non-leap years. If you prefer otherwise you will probably have to add a special condition for this;

• Finally, if you want to use this in other SQL "dialects", as in Mysql for example, exchange IIF for IF.

Answer 3

Thinking about real life, when we want to know our age, we just think of it this way:

if I was born in 1990: I am 29 years old or I am 28 years old (Difference in years between the year of my birth and the current year).

I will be 29 if I have already made birthday this year (if the current date in relation to day and month has passed my birthday) and;

I’ll be 28 if I haven’t had my birthday this year.

Following this reasoning, we managed to arrive at this logic, in sql:

DECLARE @dataNascimento Date = '2016-1-1';
DECLARE @dataReferencia Date = '2020-12-31'

select 
(
    CASE 
        WHEN 
        MONTH(@dataReferencia) > MONTH(@dataNascimento) -- *1
        OR
        -- *2
        (
            MONTH(@dataReferencia) = MONTH(@dataNascimento) 
            AND DAY(@dataReferencia) >= DAY(@dataNascimento) 
        )
    THEN DATEDIFF(YEAR, @dataNascimento, @dataReferencia) 
    ELSE DATEDIFF(YEAR, @dataNascimento, @dataReferencia) -1 END
) AS IDADE;

*1 : Suffice it that The month of the current date is greater than the month of my birthday, which we should consider having already made birthday in the current year (dataReference). *2 : Or, as the month of the current date is not yet greater than the month of my birthday, the months should be equal and the day of the current month should be greater or equal to the day of my birthday.

Now just test with a few examples.

Obs: considering who was born on 29/02, this person would always have birthday on 01/03 for non-leap years (?) . The logic applied follows this reasoning.

Answer 4

You can pick up today’s date using getdate() and subtract the bank record date using DATEDIFF:

SELECT DATEDIFF(hour, DataNascimento, getdate()) / 8766 FROM Pessoa

8766 is the number of hours in a year (approximately, as there are leap years).

Source: https://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate.

Answer 5

The most accurate method of calculating age no SQL Server is to consider the day of birth and the base day of the calculation and calculate the difference of years with DATEDIFF. The function below performs the calculation:

IF OBJECT_ID('calcular_idade', 'FN') IS NULL
BEGIN
  EXEC('CREATE FUNCTION calcular_idade() RETURNS INT AS BEGIN RETURN 1 END');
END;
GO

ALTER FUNCTION calcular_idade(@nascimento DATE,
                              @data_base  DATE)
RETURNS INT
AS
BEGIN
  DECLARE @idade      INT;
  DECLARE @dia_inicio INT;
  DECLARE @dia_fim    INT;

  SET @data_base = ISNULL(@data_base, GETDATE()); -- Caso seja nula considera a data atual
  SET @idade = DATEDIFF(YEAR, @nascimento, @data_base);
  -- Deve ser feito dessa forma por conta do ano bissexto
  -- Por exemplo: dia 16/06 ficará 616 e 14/12 ficará 1214
  SET @dia_inicio = (MONTH(@nascimento) * 100) + DAY(@nascimento);
  SET @dia_fim = (MONTH(@data_base) * 100) + DAY(@data_base);

  -- Se ainda não passou no ano base
  IF @dia_fim < @dia_inicio
  BEGIN
    SET @idade = @idade - 1;
  END;

  RETURN @idade;
END;
GO

The call with the current date (07/28/2017) result 29 years for the date of birth 16/06/1988 would be the following:

SELECT dbo.calcular_idade('1988-06-16', '2017-07-28') AS idade

Resulting in:

╔═══╦═══════╗
║   ║ idade ║
╠═══╬═══════╣
║ 1 ║ 29    ║
╚═══╩═══════╝

---

ISNULL

Replaces NULL at the specified replacement value.

---

GETDATE

Returns the date/time stamp of the current database system as a value datetime without the time zone shift of the database. This value is derived from the operating system of the computer on which the SQL Server instance is running.

---

DATEDIFF

Returns the count (signal integer) of the specified limits of DATEPART cross-checked between the specified parameters startdate and enddate.

---

MONTH

Returns an integer representing the month of date specified.

---

DAY

Returns an integer representing the day (the day of the month) of date specified.

Answer 6

declare @dataNasc datetime ='1973-06-03'

SELECT 
idade= convert (int, convert( decimal,convert (varchar( 10), getdate(), 112)) /10000 - convert( decimal,convert (varchar( 10), @dataNasc , 112)) /10000 )

Answer 7

Dear friends, I ended up developing this script to run in Access:

iif(year(Date())-year([DATA DE NASCIMENTO])<=0,0,iif(month(date())>month([DATA DE NASCIMENTO],year(Date())-year([DATA DE NASCIMENTO],iif(day(date())>day([DATA DE NASCIMENTO],year(date())-year([DATA DE NASCIMENTO],(year(date())-year([DATA DE NASCIMENTO])-1))) as IDADE

It ran perfectly in version 360, where [DATE OF BIRTH] must be the field in the table or view (query) that contains the date of birth of the person to be calculated age. I tested even with Bi-Sixth and works well.

Answer 8

I did it in a different way, very simple, but I don’t know if it can bring some kind of mistake, regarding leap year or something, take a look and comment, okay if you have any problems, if you could let me know!

declare @data1 datetime set @data1 = '02/02/2000'
declare @data2 datetime set @data2 = '12/28/2000'
declare @data3 datetime set @data3 = '12/28/1977'


select year(GETDATE() - @data1)-1900 union 
select year(GETDATE() - @data2)-1900 union 
select year(GETDATE() - @data3)-1900