CODEWITHSUNDEEP
sql-serverdatediffcalculated-field
Nils
Nils

Reputation: 514

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.

Upvotes: 0

Views: 1551

Answers (3)

irockyoursocks
irockyoursocks

Reputation: 21

When using "yyyy" in DateDiff only the year parts of the dates are used to calculate the difference. The month and day are omitted. This will produce results that are sometimes correct and sometimes incorrect by one year.

Try using this instead.

SELECT Int((#2016-07-23#-#1963-07-22#)/365.25);

Upvotes: 0

user4843530
user4843530

Reputation:

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.

Upvotes: 1

Tom
Tom

Reputation: 747

In my experience it does work best to use the number of days between the two dates and then divide that amount by 365.25 to be exact, then round off to even years. This would give you the most precise age in years I think.

Upvotes: 2

Related Questions