Search code examples
sql-serverdatediffcalculated-field

Calculate Age of user WITH Datediff (?)

i'm trying to use the Datediff function to give me the years off a user but this is more complicated than i thought.

SELECT DATEDIFF( DD,'1963-07-22','2016-07-23')

This will give me 19360 Days i think that is because 2016 have a Leap Year and that is fine.

what i would like to do is get the YEAR and not the days.

if i change the interval from DD to YY(YYYY) it only calculates the year.

Solution

  • The correct answer to calculate someone's age, or the difference in truncated years between two dates is 

    year(@today)-year(@birthDate)+floor((month(@today)-month(@birthdate)+floor((day(@today)-day(@birthdate))/31))/12);

    This will work regardless of leap years. And correct for whether the person was born on a later month or even a later day in the same month. This will also ignore hours and minutes, as should be when calculating someone's age.