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

Calculating Age

By Lynn Pettis, (first published: 2008/06/30)

How do you calculate the age of an individual? This can become an extremely hot topic and the number of solutions provided can be numerous. Before attacking this “controversial” topic, it actually would help set some ground rules to be used in designing an aging algorithm.

The most difficult part of this, of course, is figuring out what to do with leap years. When does a person (or object or document for that matter) become another year older? For the purposes of this short article, I propose that this occurs on February 28th of non leap years.

Some Basic Date Arithmetic, SQL Style

Before diving into the aging calculation, let’s look at some very simple date arithmetic using SQL Server 2005.

First, let’s see what adding 1 year to February 28, 2008 and February 29, 2008 returns.

 declare @date1 datetime,
         @date2 datetime
 set @date1 = '20080228'
 set @date2 = '20080229'
 select dateadd(yy, 1, @date1), dateadd(yy, 1, @date2)


2009-02-28 00:00:00.000 2009-02-28 00:00:00.000

Interesting, adding 1 year to both dates results in the same date. Let’s see what happens when you now add 4 years.

 declare @date1 datetime,
         @date2 datetime
 set @date1 = '20080228'
 set @date2 = '20080229'
 select dateadd(yy, 4, @date1), dateadd(yy, 4, @date2)


2012-02-28 00:00:00.000 2012-02-29 00:00:00.000

Now that is interesting, both dates aren’t the same. This is what would be expected.

Now, let’s look at DATEDIFF and see how it works a little.

 declare @date1 datetime,
         @date2 datetime
 set @date1 = '20080229'
 set @date2 = '20090228'
 select datediff(yy, @date1, @date2)



But wait, what is the result of the following?

 declare @date1 datetime,
         @date2 datetime
 set @date1 = '20081231'
 set @date2 = '20090101'
 select datediff(yy, @date1, @date2)



Wait! That isn’t right, there isn’t a year between those two dates, something must be wrong. As you can see, the DATEDIFF function returned the number of year’s difference between the year values of the two dates, not the actual number of years between the two dates.

We can use this information to create a simple algorithm:

Age = datediff(yy, date_of_birth, Today)

– (if Today < dateadd(yy, datediff(yy, date_of_birth, Today),date_of_birth)

then 1 else 0

Or, to put it in to T-SQL:

 declare @dob datetime,
         @age int,
         @day datetime
 set @day = '2008-02-28'
 set @dob = '2007-03-01'
 set @age = datediff(yy,@dob,@day) -
            case when @day < dateadd(yy,datediff(yy,@dob,@day), @dob) then 1 else 0 end
 select @age


This is just one method to use to calculate the age of person in code. There are numerous other methods that can also be used. All I can suggest, is use the method that works best for you in your situation.

Total article views: 24865 | Views in the last 30 days: 2
Related Articles

Date Manipulation with DATEADD/DATEDIFF

Learn how to use Dateadd/Datediff functions to manipulate dates in this short article from Seth Phel...



Let’s talk about the DATEDIFF vs. DATEADD functions in SQL Server. Each one performs a different tas...


where date1 > ? AND date2

hi. the where condition in a dts package is as follows: where date1 > ? AND date2 <= GETDATE()   ...


Datediff versus Convert for datetime comparison (ignoring time part)

Will DateDiff give better performance than the Convert function ?


BETWEEN date1 AND date2 Not returning data for date2

I'm using a WHERE moddate BETWEEN '06/01/2008' AND '06/30/2008' clause to extract detail records fro...