How do I calculate age from date of birth?

DATEDIFF is the function to use. However, if the birth date hasn’t yet occurred this year, the age calculated by DATEDIFF will be off by 1. For example, imagine your birthday is Dec 31st and you were born in 1950. If today is Oct 1st, 2000, subtracting the year portions of the two dates (2000-1950) would indicate that your were 50 years old. DATEDIFF returns an integer representing the number of date parts between two dates. If the start date falls after the end date, the result is negative. DATEDIFF only counts boundaries. Hence when we calculate how many years elapsed between December 31, 1999, and January 1, 2001, we will get, "2."

Here is the calculation using DATEDIFF and not accounting for whether the birth date has been reached this year:

DECLARE @BIRTHDATE SMALLDATETIME, @AGE SMALLDATETIME
SELECT @BIRTHDATE='12/10/1990', @AGE='6/24/2001'
SELECT DATEDIFF(YY,@BIRTHDATE,@AGE) AS 'Calculate age using straight DATEDIFF'

This is one way we can fix the above query:

SELECT DATEDIFF (YY, @BIRTHDATE, @AGE) -
CASE WHEN (MONTH(@BIRTHDATE)=MONTH(@AGE) AND DAY(@BIRTHDATE) > DAY(@AGE)
OR MONTH (@BIRTHDATE) > MONTH (@AGE))
THEN 1 ELSE 0 END AS 'Calculate age correcting DATEDIFF'

Here are the results:

Calculate age using straight DATEDIFF

11

Calculate age correcting DATEDIFF

10

Back

 

Advertisement

Free Magazine