Best way to Calcluate Age - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Best way to Calcluate Age

Author

Topic

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-03-29 : 16:41:14

Ive seen a few methods, whats best?

1.

SELECT FLOOR(DATEDIFF(DAY, @BirthDate, @TargetDate) / 365.25)

Some say this does not work on leap years.

Ive seen things similar to...

2.

DateDiff(yy, 0, DateAdd(d,0,Datediff(d, @BirthDate, @targetDate))) as Age

I am trying to get a persons age at a certain point in time.

Thanks!

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2010-03-29 : 16:53:20

try (for age today)
DATEDIFF(YY,DATEOFBIRTH,GETDATE()) - CASE WHEN GETDATE() < DATEADD(YY, DATEDIFF(YY,DATEOFBIRTH,GETDATE()), DATEOFBIRTH) THEN 1 ELSE 0 END

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-29 : 16:59:15

DATEDIFF( yyyy, @DateOfBirth, @PointInTime) - CASE
WHEN MONTH(@DateOfBirth) > MONTH(@PointInTime) THEN 1
WHEN DAY(@DateOfBirth) > DAY(@PointInTime) THEN 1
ELSE 0 END

There are 10 types of people in the world, those that understand binary, and those that don't.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-03-29 : 17:20:28

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762&SearchTerms=Finding%20Age

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-03-29 : 17:54:11

Thanks appreciate the help, I had found that article before, I just did not know if there was a universally accepted version. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources