How to calculate the age of a person in SQL Server?

The most correct way I know is:

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

This form is more correct than using the hours of a year (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 hours will inform as if the person has already had a birthday, when in fact it did not (unless it is executed exactly on the time 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

I prefer to go to the more extensive and secure side and use a function with traditional old calculation logic, so as not to have 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 in the database. I made with variables the example so that you can easily test using DECLARE (however, remember by @ before variables).

Notes:

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

• Since we do not consider time in the calculation of age, the IIF is adjusted so that the number of years of age" goes up " 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 being 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", such as MySQL for example, exchange IIF for IF.

You can take today's date using getdate() and subtract the date from the bank record using DATEDIFF:

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

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

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

The most accurate method of calculating age in SQL Server is to consider the day of birth and the base day of the calculation and calculate the difference in 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 (28/07/2017) result 29 years for the date of birth 16/06/1988 would be as follows:

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

Resulting in:

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

ISNULL

Replaces NULL with the replacement value unspecified.

GETDATE

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

DATEDIFF

Returns the count (integer with sign) of the specified limits of DATEPART crossed between the parameters specified startdate and enddate.

MONTH

Returns an integer that represents the month of the specified date.

DAY

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

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 )

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

Ran perfectly in version 360, where [Date of birth] should be the field in the table or view (query) containing the date of birth of the person to be calculated age. I tested even with Bi-sixth and it works well.

I did a different way, very simple, but I don't know if it can bring some kind of error, regarding leap year or something else, take a look and comment, ok if you have any problems, if you can communicate with me it would be very good!

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