SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Simple Age Calculate
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

flamblaster
Constraint Violating Yak Guru

380 Posts

Posted - 03/03/2013 :  01:35:38  Show Profile  Reply with Quote
Hey guys,

I've seen and used a lot of elegant queries to calculate age. I was thinking about it today though and it seems you should be able to do simple datediff and divide by 365.25 to get a person's age like this:

declare @BirthDate date, @CheckDateA date, @CheckDateB date
select
@Birthdate='1971-04-14'
,@CheckDateA='2013-04-13'
,@CheckDateB='2013-04-14'
select CAST(datediff(dd, @BirthDate, @CheckDateA)/365.25 AS INT),
CAST(datediff(dd, @BirthDate, @CheckDateB)/365.25 AS INT)

Am I oversimplifying this? It seems like this is a relatively easy way to get someone's age.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 03/03/2013 :  04:39:00  Show Profile  Reply with Quote
Yes, datediff would be the way to do it. You can use different datepart depending on your requirement. YYYY would give you the years if that's what you want.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 03/03/2013 :  11:25:31  Show Profile  Reply with Quote
It would not be perfect - for example, this is not quite right, because of 2012 being a leap year:
declare @BirthDate date, @CheckDateA date, @CheckDateB date
select 
@Birthdate='20110225'
,@CheckDateA='20120225'
,@CheckDateB='20130225'
select CAST(datediff(dd, @BirthDate, @CheckDateA)/365.25 AS INT),
CAST(datediff(dd, @BirthDate, @CheckDateB)/365.25 AS INT)
On Feb 25, 2012 the age is reported as 0, yet on Feb 25, 2013, the age is reported as two.

An unambiguous way to calculate age (which relies on the ordering when date is expressed in YYYYMMDD format being correct when expressed as an int) is as follows:
declare @BirthDate date, @CheckDateA date, @CheckDateB dateselect 
@Birthdate='20110225'
,@CheckDateA='20120225'
,@CheckDateB='20130225'
SELECT (CAST(CONVERT(CHAR(8), @CheckDateA, 112) AS INT)
   - CAST(CONVERT(CHAR(8), @BirthDate, 112) AS INT) )/10000,
   (CAST(CONVERT(CHAR(8), @CheckDateB, 112) AS INT)
   - CAST(CONVERT(CHAR(8), @BirthDate, 112) AS INT) )/10000

Edited by - James K on 03/03/2013 11:41:01
Go to Top of Page

flamblaster
Constraint Violating Yak Guru

380 Posts

Posted - 03/03/2013 :  11:56:07  Show Profile  Reply with Quote
Thanks for both your comments! James, that makes sense...this is what I was looking for. I use the latter that you posted right now for age calculation and know that it works well. I just like trying to simplify wherever possible. But your explanation makes sense. I thought I was handling the leap year by using 365.25 instead of 365. I see why that doesn't work now though.

And Lozt...using Datepart for years wouldn't work because it doesn't handle the difference between "before" anniversary and "after" anniversary. It just compares the raw number of years between the start date's year and the end date's year.

Thanks!

Edited by - flamblaster on 03/03/2013 12:00:21
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 03/03/2013 :  17:08:08  Show Profile  Reply with Quote
You are very welcome - glad you have it working the way you want.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000