Calculating Age from Birthday - TSQL, Oracle, and any others

Question

I frequently see people who assume that DATEDIFF(YEAR, date_of_birth, GETDATE()) will produce the current age of the person.

Unfortunately this is not correct. The DATEDIFF function, at least in SQL Server, does not count the number of full years between two dates - it counts the number of calendrical boundaries crossed.

That is, in the case of the YEAR parameter, DATEDIFF counts the number of 1-Januaries crossed. With the MONTH parameter, it would count the number of 1sts of the month crossed.

So for example, a baby born on 31-Dec-17 would already be "1 years old", when in fact they do not turn 1 years old until 31-Dec-18, and they are currently 0 years old.

The use of the DATEDIFF function in this way is equivalent to writing YEAR(GETDATE()) - YEAR(date_of_birth), which of course takes no account of where the person's birthday falls inside the year.

Having reviewed a good sample of other questions here, there does not appear to be a definitive answer, and there are many answers that are quite wrong.

So my question is, what is the simplest expression that, given a person's date of birth and a reference date, calculates the age of a person as of that reference date?

I'm looking for an answer in the following terms:

• The answer should be an inline expression - capable of being selected or incorporated into a where-clause.

• The answer should be robust and have no erroneous corner cases - however infrequent or implausible those cases are. It should work for newborn babies and for persons that would be centuries old, should cope with leap years, and ideally should be capable of representing the period prior to birth as a minus number (with the person becoming -1 years old on the day before they were born).

• The answer should be numerically precise - it should not rely on using an average number of days per year or dividing by magic numbers, and rounding off the error. I believe all trivial approaches which use rounding, have corner cases and are therefore not robust, but I stand to be corrected.

• For those with birthdays falling on the 29-Feb, it should treat their age as incrementing on 29-Feb if the reference year is a leap year, or it should treat their age as incrementing on 1-Mar if the reference year is not a leap year.

• The answer should disregard any time component of either the birthdate or the reference date - that is, the person should be assumed to have been born at midnight, and their age should increment at midnight on subsequent birthdays.

I'm interested in an answer for SQL Server particularly, but would also be interested to see answers for any other database platform.

EDIT: I appreciate the answers that have been given and the links to previous questions. This question has now been marked as a duplicate.

What strikes me is that the accepted answer to the link given, is cluttered and begins with an incorrect implementation, and the comments on it refer to its implementation being incorrect - it was only after I read it carefully that I saw it had been updated with a correct implementation (sandwiched between a treatment of "decimal age" which is not related to my question). The third answer, which has also attracted a substantial number of up-votes, is also incorrect. There are also a myriad of other answers of varying quality.

I've also asked for an answer that, ideally, correctly handles "negative ages" in some way (where the reference date is before the birth date), and the linked answers do not. Therefore, whilst my question treats the same subject, it is not a duplicate, as I've imposed several additional constraints that are not present in that question, and which would invalidate (in one way or another) all the answers given on that older question.

To be honest, given how many subtly wrong answers abound (as I alluded to above in my first paragraph), I would have thought this question would get more upvotes and more interest, rather than a downvote and 3 votes to close.

Answer 1

in Oracle you can do it like this,

SELECT FLOOR(MONTHS_BETWEEN(:given_date, :birth_date)/12) 
  FROM DUAL;

if birthday is Feb 29 and March 1 is the considered birthday if the year is not a leap year, you can use query below,

SELECT FLOOR(
          MONTHS_BETWEEN (
            CASE TO_CHAR(TO_DATE(:birth_date, 'DD-MON-YYYY'), 'fmMON-DD')
                WHEN 'FEB-29' THEN
                    TO_DATE(:given_date, 'DD-MON-YYYY') - 1
                ELSE 
                    TO_DATE(:given_date, 'DD-MON-YYYY')
             END,
             TO_DATE(:birth_date, 'DD-MON-YYYY'))
               /12) years_old
  FROM DUAL;

in SQL Server, I cannot help you but I found this link, http://www.sqlines.com/oracle-to-sql-server/months_between, maybe it can help you,

Answer 2

A correct answer

Cobbled together from the sources given, this is the correct answer for SQL Server (from 2012):

IIF(reference_date < date_of_birth, -1, CAST(CONVERT(CHAR(8), reference_date, 112) AS INT) - CAST(CONVERT(CHAR(8), date_of_birth, 112) AS INT) / 10000)

This converts a date into an ISO date string (YYYYMMDD), casts the string to and int, and then subtracts one from the other - if the month and monthday of birth are higher than the reference date, this subtraction borrows a digit from the year component. We then integer-divide by 10,000 to produce the whole-years difference. This works seamlessly across leap years. Time parts are automatically truncated in the conversion. CHAR(8) is used as the string is fixed length.

For other platforms, I would recommend this SQL Server solution be adapted. For SQL Server pre-2012, replace the IIF with a CASE.

There are of course a variety of other correct ways, but this appears to be the inline solution that is most economical with syntax, and I have not seen any better.

---

Incorrect answers

I just thought I'd take a minute to address incorrect answers in various sources, for the benefit of any future readers.

1. DATEDIFF(YEAR, date_of_birth, reference_date)

As mentioned in my question, this seems to be the first approach many try. Unfortunately it only counts the difference in the year-part of the date, so that a baby born 31-Dec-17 turns 1 years old on 01-Jan-18. It's good but it's not right.

2. FLOOR(DATEDIFF(DAY, date_of_birth, reference_date) / 365.25)

This seems to be another common approach - and was also given in a comment on my question.

For a baby born 28-Feb-17, properly calculated they turn 1 years old on 28-Feb-18 - their next birthday. 365 days elapse between the two dates. 365 / 365.25 = 0.99. Floored it becomes zero. The baby is treated as turning 1 on 1-Mar-18 - a day too late.

Further refining the "days per year" divisor after the decimal point does not help, as any divisor above 365 exactly, will fail in this case. It's good but it's not right.

3. DATEDIFF(HOUR, date_of_birth, reference_date) / 8766

A variation on (2) is to use hours instead of days. There is a fixed number of hours in every day, therefore 8766/24 = 365.25. We see this solution is equivalent to (2). It's good but it's not right.

4. CONVERT( INT, ROUND(DATEDIFF(HOUR, date_of_birth, reference_date) / 8766.0, 0) )

Another variation is to use rounding on the hours. This suffers from exactly the same defect as (3) for the same reasons, but additionally if a time component is involved, then it rounds up the last 30 minutes of the day prior to the birthday. It's good but it's not right.

5. DATEPART(DAYOFYEAR, ...)

Others have explored whether DAYOFYEAR can provide a solution. Unfortunately, due to leap years, the standard DAYOFYEAR does not map consistently to specific dates (months and monthdays) of the year. It's good but it's not right.

---

A maybe answer

6. FLOOR( MONTHS_BETWEEN(reference_date, date_of_birth) / 12 )

This Oracle-specific solution falls down when the date of birth is 29-Feb (in a leap year), and the reference date is 28-Feb (in a non-leap year), in that it treats the birthday as 28-Feb in non-leap years.

That actually may be closer to the common convention on when a birthday is celebrated for leap-year babies, but I've specified in my question that in such cases the birthday for the purposes of calculating a person's age should be treated as 1-Mar (which is consistent with UK law).

The MONTHS_BETWEEN also has other quirks which might be undesirable for age calculations (for example, if it is used to calculate age as a number of months, such as for babies less than 1 years old, where the "birthday" falls on or after the same monthday in subsequent months).

It's good and it may be considered right in some contexts!

---

Summary

There are of course other ways to implement a correct solution than the one given here, but heed should be taken of the litany of incorrect answers (and many more overwrought examples that I do not consider here), and any innovation or alternative calculation should be thoroughly tested and compared against one that is already known to work.

Answer 3

As of Oracle, I use such a function; I've read conditions you mentioned (i.e. that you should be able to use it in a WHERE clause; well, you can use a function there. If you don't want it, no problem in using the whole code either, but that would look ugly). Output can be modified; I used that format, you can change it if you want.

Here you are:

SQL> create or replace function f_age (par_birthdate in date)
  2     return varchar2
  3  is
  4     retval   varchar2 (30);
  5  begin
  6     retval :=
  7           trunc (months_between (sysdate, par_birthdate) / 12)
  8        || ' years '
  9        || lpad (trunc (mod (months_between (sysdate, par_birthdate), 12)),
 10                 2,
 11                 ' ')
 12        || ' months '
 13        || lpad (
 14              trunc (
 15                   sysdate
 16                 - add_months (
 17                      par_birthdate,
 18                          trunc (months_between (sysdate, par_birthdate) / 12)
 19                        * 12
 20                      + trunc (
 21                           mod (months_between (sysdate, par_birthdate), 12)))),
 22              2,
 23              ' ')
 24        || ' days ';
 25
 26     return (trim (retval));
 27  end f_age;
 28  /

Function created.

Scott's EMP table:

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> select ename, hiredate, f_age(hiredate) result
  2  from emp;

ENAME      HIREDATE   RESULT
---------- ---------- ------------------------------
KING       17.11.1981 36 years  2 months 27 days
BLAKE      01.05.1981 36 years  9 months 12 days
CLARK      09.06.1981 36 years  8 months  4 days
JONES      02.04.1981 36 years 10 months 11 days
SCOTT      09.12.1982 35 years  2 months  4 days
FORD       03.12.1981 36 years  2 months 10 days
SMITH      17.12.1980 37 years  1 months 27 days
ALLEN      20.02.1981 36 years 11 months 24 days
WARD       22.02.1981 36 years 11 months 22 days
MARTIN     28.09.1981 36 years  4 months 16 days
TURNER     08.09.1981 36 years  5 months  5 days
ADAMS      12.01.1983 35 years  1 months  1 days
JAMES      03.12.1981 36 years  2 months 10 days
MILLER     23.01.1982 36 years  0 months 21 days

14 rows selected.

SQL>

Answer 4

Pseudo:

if get month - date of birth month>=0 && get day - birthday >=0
        Age = get year - date of birth year
else
        Age = get year - date of birth year - 1