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

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

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?

Solution

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.