SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Issues With Calculating Current Age Using DateDiff (Rounding?)

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:


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.

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.

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).


No comments.

Leave a Comment

Please register or log in to leave a comment.