-1

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 something 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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Jaigus
  • 1,422
  • 1
  • 16
  • 31

3 Answers3

0

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
sa-es-ir
  • 3,722
  • 2
  • 13
  • 31
0

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.

Lemonina
  • 712
  • 2
  • 14
0

Here's another way to get your desired result:

SELECT (CAST(FORMAT(CAST('02-01-2020' AS datetime2),'yyyyMMdd') AS int) - CAST(FORMAT(CAST(person.birthdate AS datetime2),'yyyyMMdd') AS int))/10000 AS calculated_age
FROM PersonTable
jigga
  • 558
  • 4
  • 16