Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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)

Result:

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)

Result:

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)

Result:

1

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)

Result:

1

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

Conclusion

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: 23999 | Views in the last 30 days: 38
 
Related Articles
ARTICLE

Date Manipulation with DATEADD/DATEDIFF

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

FORUM

where date1 > ? AND date2

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

FORUM

Datediff versus Convert for datetime comparison (ignoring time part)

Will DateDiff give better performance than the Convert function ?

FORUM

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

FORUM

DATEDIFF WEEKS!!!

DATEDIFF WEEKS !!!

Tags
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones