What is an efficent way of calculating a person's age at a particular time in the past?-CodePudding

I am trying to calculate the age of a person given a particular date in the past; let's just say '02-01-2020' for this example.

I have somethign like this:

SELECT person.name,
 CASE
   WHEN DATEADD(YEAR, DATEDIFF(YEAR, person.birthdate, '02-01-2020'), person.birthdate) < '02-01-2020'
   THEN DATEDIFF(YEAR, person.birthdate, '02-01-2020')-1
   ELSE DATEDIFF(YEAR, person.birthdate, '02-01-2020')
 END AS calculated_age,
FROM   PersonTable
[...]

I don't think this works for potential birthdates that fall after '02-01-2020', and I was wondering if there is just a better way to do this?

CodePudding user response：

I use this approach in my current project

DECLARE @Date DATETIME = '2020-02-01' 

select DATEDIFF(YEAR,person.birthDate, @Date) - 
  CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, person.birthDate, @Date), person.birthDate) > @Date THEN 1 ELSE 0 END

CodePudding user response：

I would get the difference between the two dates in days and then divide this number by 365.25 accounting for leap years.

SELECT person.name,
    FLOOR(DATEDIFF(DAY, person.birthdate, '02-01-2020') / 365.25) AS calculated_age,
FROM   PersonTable
[...]

This might cause some inaccuracies if you are using these results in other calculations due to the fractional part of the year.