Okay, so you’ve got a database full of people and their birthdays, and you’d like to calculate someone’s age. Would you, like me, immediately look to the Transact-SQL DATEDIFF function as a means of quickly and painlessly performing this calculation? That’s what I thought!

This is the statement that you would expect to do the job for you:

SELECT DATEDIFF(yy, @BirthDate, GETDATE());

Now, imagine your surprise when, instead of getting the behaviour you expect (i.e. the difference between the two dates, expressed in years), you get a number that, until the person’s birthday falls for the current year, is always 1 too large?

If that’s the case, you’ve fallen into the same trap that I did. If you read into it, you’ll find that DATEDIFF doesn’t really calculate the difference between two dates at all. Instead, it counts the number of date and time boundaries crossed between the two dates specified.

This means that, the larger the increment you ask for (i.e. years in this case), the less precise the function is in the calculation of its result. In fact, the statement above is equivalent to:

SELECT YEAR(GETDATE()) - YEAR(@BirthDate);    -- produces the same result

…which we know is definitely not the correct way to calculate a person’s age!

We run into the same problem if we ask for the results expressed in terms of months instead of years (and then divide by 12 to get an age in years), but the problem only occurs for the first part of the month in which the person’s birthday falls. If we try to get the result in days instead of months or years, we have to start accounting for leap years and the solution becomes too complex.

The easiest way to calculate a person’s age in SQL, therefore, it to use some simple conditional logic based on the day, month and year components of the date of birth:

DECLARE @Age int;
SET @Age = YEAR(GETDATE()) - YEAR(@BirthDate);
IF (MONTH(GETDATE()) < MONTH(@BirthDate)) OR (MONTH(GETDATE()) = MONTH(@BirthDate) AND DAY(GETDATE()) < DAY(@BirthDate))
    SELECT @Age - 1;
ELSE
    SELECT @Age;

Complete Function

I often deal with information about people who may either be living or deceased. For deceased persons, we’re still interested in calculating age, but we want their age as at their date of death. It’s also useful to calculate a person’s age relative to a certain date, in a “on [x] date, you were/will-be [y] years old” manner. The function below can be used to calculate age relative to any date:

CREATE FUNCTION fn_CalculateAge(@BirthDate datetime, @DeathDate datetime)
RETURNS int
AS
BEGIN

-- if @DeathDate is passed NULL, use today's date
SET @DeathDate = ISNULL(@DeathDate,GETDATE());
DECLARE @Age int;

IF @DeathDate > @BirthDate
BEGIN
    SET @Age = YEAR(@DeathDate) - YEAR(@BirthDate);
    IF (MONTH(@DeathDate) < MONTH(@BirthDate)) OR (MONTH(@DeathDate) = MONTH(@BirthDate) AND DAY(@DeathDate) < DAY(@BirthDate))
        RETURN @Age - 1;
END

-- function will return NULL if the dates are in the wrong order, or if the date-of-birth is NULL
RETURN @Age;

END

Final Words

Don’t dismiss the DATEDIFF function entirely in light of its semantic limitations; it’s still useful as long as you remember that it counts date/time boundaries instead of giving a true “difference” between two dates. When calculating ages, be sure to steer clear of it, however.