How to determine a person's age using TSQL

04-SEP-2011, last improvement 17-MAY-2012
If you have someone's date of birth, and want to determine how old that person is today, intuition would suggest to simply use a statement like this:
-- RETURNS INCORRECT RESULTS, SO DON'T USE IT

SELECT DATEDIFF(year, date_of_birth, CURRENT_TIMESTAMP) AS Age
FROM my_table
Unfortunately, this will return incorrect results in up to 50% of all cases, because DATEDIFF(year,...) calculates the number of boundary crossings. So if the date of birth is 2000-12-01, and the current date is 2011-09-04, the query above would say that the person is already 11 years old, instead of the actual age, which is 10.
Some people try to tackle this problem by calculating the difference in days, for example:
-- RETURNS INCORRECT RESULTS, SO DON'T USE IT

SELECT FLOOR(DATEDIFF(day, date_of_birth, CURRENT_TIMESTAMP) / 365.25) AS Age
FROM my_table
Again, this does not always return correct results. Not all years have the same number of days, and dividing by 365.25 gives rouding errors in 0.104% of all cases. For example, if you are born in 17 January 1999, and today is 17 January 2012, according to the above query you would still be 12 instead of turning 13.

Simplest solution

The solution that is simplest to write is the one below

-- Most compact solution

SELECT (0+CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)-CONVERT(CHAR(8),date_of_birth,112))/10000 AS Age
FROM my_table

The CONVERT format 112 returns a date in the format YYYYMMDD. So it first converts the date of birth and the current date into this string format. Then it relies on implicit conversion to integer to calculate the difference between them, and the division with 10000 will rebase this result as the number of passed years, in other words, into the age.

I am not sure who first came up with it, but I have seen it first from Anith Sen.

Fastest solution

The fastest solution is a tie between the two below

-- Fastest solution 1

SELECT YEAR(CURRENT_TIMESTAMP) - YEAR(date_of_birth) -
       (CASE WHEN (MONTH(date_of_birth) > MONTH(CURRENT_TIMESTAMP))
               OR (MONTH(date_of_birth) = MONTH(CURRENT_TIMESTAMP)
              AND DAY(date_of_birth) > DAY(CURRENT_TIMESTAMP))
             THEN 1 ELSE 0 END) AS Age
FROM my_table


-- Fastest solution 2

SELECT DATEDIFF(year, date_of_birth, CURRENT_TIMESTAMP) - CASE WHEN
       DATEADD(year, DATEDIFF(year,date_of_birth,CURRENT_TIMESTAMP), date_of_birth) > CURRENT_TIMESTAMP
       THEN 1 ELSE 0 END AS Age
FROM my_table

Other solutions


All other available methods perform worse, at least on my system. Examples of these other solutions can be found below

-- suboptimal solutions

SELECT DATEDIFF(yy, date_of_birth, CURRENT_TIMESTAMP) -
        CASE WHEN (MONTH(CURRENT_TIMESTAMP) * 100 + DAY(CURRENT_TIMESTAMP)) <
                  (MONTH(date_of_birth)     * 100 + DAY(date_of_birth))
             THEN 1 ELSE 0 END AS Age
FROM my_table

SELECT YEAR(CURRENT_TIMESTAMP) - YEAR(date_of_birth)
       - CASE WHEN MONTH(CURRENT_TIMESTAMP) < MONTH(date_of_birth) THEN 1
              WHEN MONTH(CURRENT_TIMESTAMP) > MONTH(date_of_birth) THEN 0
              WHEN   DAY(CURRENT_TIMESTAMP) <   DAY(date_of_birth) THEN 1
                                                                   ELSE 0 END AS Age
FROM my_table

SELECT CASE WHEN (MONTH(CURRENT_TIMESTAMP)*100)+DAY(CURRENT_TIMESTAMP) >= (MONTH(date_of_birth)*100)+DAY(date_of_birth)
            THEN DATEDIFF(Year,date_of_birth,CURRENT_TIMESTAMP)
            ELSE DATEDIFF(Year,date_of_birth,CURRENT_TIMESTAMP) - 1 END AS Age
FROM my_table

Should you have a correct alternative solution that either performs better or is shorter to write, then please contact me (see below).

User Defined Function (UDF)


Below is a scalar user defined function to calculate the age. For maximum performance, use any of inline solution that were mentioned earlier, because they will all performance one order or magnitude better than a UDF solution.

-- Scalar UDF

CREATE FUNCTION dbo.Age(@dob datetime)
RETURNS int WITH SCHEMABINDING AS
Begin
  Declare @age int
  Set @age=(SELECT DATEDIFF(year, @dob, CURRENT_TIMESTAMP))
  If DATEADD(year,@age,@dob) > CURRENT_TIMESTAMP
    Set @age=@age-1
  Return @age
End

SELECT dbo.Age(date_of_birth)
FROM my_table


Back to SQL Server main menu. Mail your comments to gertjans@xs4all.nl.