Age calculation with SQL Server

There seem to be many different methods being suggested to calculate an age in SQLServer. Some are quite complex but most are simply wrong. This is by far the simplest and accurate method that I know.

Declare @Date1 datetime
Declare @Date2 datetime

Select @Date1 = '15Feb1971'Select @Date2 = '08Dec2009'select CASE
WHEN dateadd(year, datediff (year, @Date1, @Date2), @Date1) &gt; @Date2
THEN datediff (year, @Date1, @Date2) - 1
ELSE datediff (year, @Date1, @Date2)END as Age

Like this:

Be the first to like this post.

May 19, 2010 - Posted by yrbyogi | Asp.net, SQL

3 Comments »

Please can u explain how it actually works?

Comment by Sunny | February 24, 2011 | Reply
Thank You!

Comment by Priya Shah | March 22, 2011 | Reply
Case: I have a table with cus_id int,tagdate date,flag varchar(1),workeddate date.

Sample data: cus_id tagdate workeddate flag
69987 2011-02-22 2011-02-23 Y
69745 2011-02-22 2011-02-23 Y
69762 2011-02-23 2011-02-24 Y
69719 2011-02-24 2011-02-26 Y
69958 2011-02-24 2011-02-24 N

I need to have output like this:

For run date:- 2/22/2011

2/22/2011
2

For run date:- 2/23/2011

2/23/2011 2/22/2011
1 0

For run date:- 2/24/2011

2/24/2011 2/23/2011 2/22/2011
2 0 0

For run date 2/25/2011

2/25/2011 2/24/2011 2/23/2011 2/22/2011
0 1 0 0

For run date 2/25/2011 :– count comes in 24 date because of the flag. I need to parameterized this query in such a way that if I want to run for the back date it must give me the same count as it was showing for that particular date.

How will I calculate this aging? Please help.

Comment by Ronak | August 19, 2011 | Reply