SQL Server DATEDIFF round up the YEAR difference. How to round it down?

Question

I have a problem that I tried googling but most questions are about rounding down hours or minutes.

I'm checking birthday dates for users and I have two dates that are 99.3 years apart. That means that the user is 99 years old but this piece of code:

DATEDIFF(YEAR, r.BirthDate, ISNULL(@Date,GETDATE()))

returns a value of 100. Is there a way to round the value down?

Answer 1

This is how fixed it.

Instead of:

DATEDIFF(YEAR, r.BirthDate, ISNULL(@Date,GETDATE()))

use this:

FLOOR(DATEDIFF(week, r.BirthDate, ISNULL(@Date,GETDATE())) / 52.177457)

Here's an article that demonstrates that this trick works like a charm.

Answer 2

You can use this logic to get the correct age:

select (case when month(birthdate) * 100 + day(birthdate) >=
                  month(getdate()) * 100 + day(getdate())
             then year(getdate()) - year(birthdate)
             else year(getdate()) - year(birthdate) - 1
        end) as age

This should be accurate, even in the presence of leap years. Basically it looks at the month-day portion of the birthdate and checks if it is on or later than today. The logic uses this information to determine the age in year.