Calculate Age of user WITH Datediff (?)

Question

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.

So what do you expect in this case? Baby's age is often cited by months, not days or years. — Code Different, Jul 23 '15 at 13:43
this answer work on any language http://stackoverflow.com/a/11942/40868 — Fredou, Jul 23 '15 at 13:56
or this one - uses simple and direct math - http://stackoverflow.com/a/16142434/4843530 — , Jul 23 '15 at 15:59

score 2 · Answer 1 · answered Jul 23 '15 at 13:46

2

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.

answered Jul 23 '15 at 13:46

Tom

747
5
16

1

This can give you the wrong age especially on the person's birthday. For example, if someone was born on today's date, for as far back as you can go, this should give you the wrong age, except if their birthdate happened to be the year before a leap year, then this gets the right age. – Jul 23 '15 at 14:26
@Tom - truncate(datediff(day,'2015-07-23','1985-07-23')/365.25) gives you 29. But it should give 30 to get the right age. For a year or two ago, this will work if you use the system date (for "today"), but if the person is more than a couple years old, even using sysdate, you will get the wrong age on their birthdate. – Jul 23 '15 at 14:55
What I proposed is the easiest way to get to right age nearly all the time. Of course this may cause a minor difference on the actual birthdate but I feel that this is a small price to pay for not having to overcomplicate the age calculation. In most cases this will do just fine. – Tom Jul 23 '15 at 20:34

score 1 · Accepted Answer · 2015-07-23T15:06:25.170

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.

score 0 · Answer 3 · answered Feb 12 '21 at 19:08

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);

Calculate Age of user WITH Datediff (?)

3 Answers3