Recently I inherited a set of SSIS packages that did a pretty simple calculation for age.  It used the following expression, which could just as easily be in T-SQL, to return back the current age of a person:

DATEDIFF("YYYY", BirthDate, GETDATE())

Seems to make sense right?  It sounds like it is saying return an integer back of the number of years between the birthdate column and the current date (GetDate()).  Well if you do a little research on how DATEDIFF actually works you will find this will not always return an accurate age.  When using DATEDIFF and the YYYY datepart it is actually showing the difference between year boundaries.  That means if today is 10/18/2009 and a person has a birthday of 11/1/2010 then they will be assigned a current age of 1 even though they’ve yet to have their first birthday.  This is because it landed under the boundaries of 2010(1/1/2010 – 12/31/2010).

So what should you do instead?  I use this expression below:

DATEDIFF("DD",BirthDate, GETDATE()) / 365

This expression is a lot more accurate although I’m sure there are still issues to be aware of even using this.  For example, during a leap year you could potentially be off by a day.  These expressions were part of a Derived Column in an SSIS package but DATEDIFF will perform the same way in TSQL.  You can use this code to test against AdventureWorksDW.

Select
Birthdate,
DATEDIFF(YYYY,BirthDate,GetDate()) as YYYY_DateDiff, 
DATEDIFF(DD,BirthDate,GetDate())/365 as DD_DateDiff
From DimEmployee
Where DATEDIFF(YYYY,BirthDate,GetDate()) !=DATEDIFF(DD,BirthDate,GetDate())/365

This query will only return back the records with inaccurate ages.  To see all records just remove the where clause.