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).*