Get Precise Age

It seems a simple thing: determine the age of a person. Given his/her birth date, and the current date, the maths are quite obvious and a human does the calculation with ease. However… it is surprising that software doesn’t often ship with native functions adequate to the task. Want to see what I mean? ūüôā

Let’s start with the basic DATEDIFF function, using year¬†as the datepart argument.

-- declare and initialize variables
DECLARE @birthdate DATE = '2000-07-31'
    , @currentdate DATE = '2016-01-01';
-- use datediff to calculate the difference
SELECT @birthdate AS 'birth'
    , @currentdate AS 'today'
    , DATEDIFF(YEAR, @birthdate, @currentdate) AS 'ageWithDateDiff';

Obviously this result does not satisfy the use case for which we employed the function. DATEDIFF returns the difference between the year values only, without any consideration of the month or day of either dates. The calculation indicates our person is 16 years old when, in fact, the person is 15 and has not yet reached his/her 16th birthday.

In response to this result I’ve often seen coded what I consider a brute-force approach to precision — maintaining the conceptual trajectory but applying it at a more granular level. Specifically, the DATEDIFF function is still employed but used with days instead of years, the result is divided by 365 (days per year) to get a fractional year value, and the result is rounded down to the nearest integer to get the age.

This approach works for the previous example:

-- declare and initialize variables
DECLARE @birthdate DATE = '2000-07-31'
    , @currentdate DATE = '2016-01-01';
-- use days to calculate the difference
SELECT @birthdate AS 'birth'
    , @currentdate AS 'today'
    , FLOOR(DATEDIFF(DAY, @birthdate, @currentdate) / 365.0) AS 'ageWith365Days';

So far so good! But the approach does not work in every case, for example:

-- declare and initialize variables
DECLARE @birthdate DATE = '2000-02-29'
    , @currentdate DATE = '2016-02-28';
-- use days to calculate the difference
SELECT @birthdate AS 'birth'
    , @currentdate AS 'today'
    , FLOOR(DATEDIFF(DAY, @birthdate, @currentdate) / 365.0) AS 'ageWith365Days';

As can be seen above, the “age calculation using days per year” method fails when a leap day is in play. But brute-force hasn’t given up just yet: let’s use 365.25 days per year to account for the leap day!

-- declare and initialize variables
DECLARE @birthdate DATE = '2000-02-29'
    , @currentdate DATE = '2016-02-28';
-- use average days per year to calculate the difference
SELECT @birthdate AS 'birth'
    , @currentdate AS 'today'
    , FLOOR(DATEDIFF(DAY, @birthdate, @currentdate) / 365.25) AS 'ageWithLeapDays';

At this point it looks like the challenge¬†is solved. However… there is still a problem; for example:

-- declare and initialize variables
DECLARE @birthdate DATE = '2001-02-28'
    , @currentdate DATE = '2016-02-28';
-- use average days per year to calculate the difference
SELECT @birthdate AS 'birth'
    , @currentdate AS 'today'
    , FLOOR(DATEDIFF(DAY, @birthdate, @currentdate) / 365.25) AS 'ageWithLeapDays';

The “365.25” days method calculates that the person is 14. In this case, however, the current date is actually the person’s 15th birthday. In other words, the calculation fails.

Is there anything else for the brute-force approach to try? Instead… let’s attempt to code the underlying rules for calculating a person’s age (or any event anniversary).

Subtracting the years is actually a very good place to start because, if the “current” month-day is on-or-after the “birth” (event) month-day, then the raw year calculation is correct. And if the “birth” month-day is before the “current” month-day, then we subtract one from the raw year calculation.

As it turns out, this comparison of month-days is quite easy; we can simply use the¬†DATEDIFF function with the DAYOFYEAR datepart argument! Of course, we still need to accommodate leap days. If we don’t consider leap days, then we will¬†find that 59th day of the year is “normally” 01-Mar, but it’s 29-Feb on a leap year.

The good news is that accounting for leap days¬†is not as complicated as it might at first seem. We can still use DAYOFYEAR as the DATEDIFF argument, but decrement the result when dealing with a leap year on either the event or current date. (Note that we don’t need to worry about it if both event and current dates are in a leap year or neither are in a leap year — in those cases, the years have the same number of days so a straight-compare works correctly.)

CREATE FUNCTION dbo.ufnGetYearDiff (@start DATE, @end DATE)
RETURNS SMALLINT
AS
BEGIN
    DECLARE @return INT
        , @s SMALLINT = DATEPART(DAYOFYEAR, @start)
        , @e SMALLINT = DATEPART(DAYOFYEAR, @end);
    -- if necessary, decrement the start date dayofyear
    IF YEAR(@start) % 4 = 0 AND YEAR(@end) % 4 != 0 AND @s > 59 SET @s -= 1;
    -- if necessary, decrement the end date dayofyear
    IF YEAR(@start) % 4 != 0 AND YEAR(@end) % 4 = 0 AND @e > 59 SET @e -= 1;
    -- decrement the year diff if the end is earlier in the year than the start
    SET @return = DATEDIFF(YEAR, @start, @end)
        - CASE WHEN @e < @s THEN 1 ELSE 0 END;
    RETURN @return;
END
GO

Let’s test it out and see if we have solved the problem.

-- declare and initialize variables
DECLARE @birthdate DATE = '2001-02-28'
    , @currentdate DATE = '2016-02-28';
-- use the custom function to calculate the age
SELECT @birthdate AS 'birth'
    , @currentdate AS 'today'
    , dbo.ufnGetYearDiff(@birthdate, @currentdate) AS 'ageFn';

Success! Does anyone have a more elegant way to solve this challenge?

PS: Before anyone yells at me, the BOL documentation for DATEDIFF very clearly states what the function does: it returns the number of specified boundaries crossed between the startdate and enddate. Nonetheless, the function doesn’t do what the name implies (or, more precisely, what many people assume).

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: