Simple Age Calculate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Simple Age Calculate

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2013-03-03 : 01:35:38

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 - 2013-03-03 : 04:39:00

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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-03 : 11:25:31

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

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2013-03-03 : 11:56:07

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!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-03 : 17:08:08

You are very welcome - glad you have it working the way you want.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources