Age Calculation - 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 2000 Forums

Transact-SQL (2000)

Age Calculation

Author

Topic

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2005-05-17 : 12:14:59

Is there any way of taking a Date Of Birth column and calculating how old someone is as of Today()?

GC

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-17 : 12:38:17

declare @dob datetime
set @dob = '19800530'
select datediff(yy, @dob, getdate())

Go with the flow & have fun! Else fight the flow

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-17 : 12:57:20

[code]
declare @dob datetime
set @dob = '19800530'
select datediff(dd, @dob, getdate())/365.00

[/code]

Brett

8-)

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-05-17 : 17:30:50

Unfortunately, I don't think either of these methods will be entirely accurate, especially on a leap year. I think this will be better:

select
case when month(getdate()) <= month(@dob) and day(getdate()) < day(@dob) then datediff(yy, @dob, getdate()) -1
else datediff(yy, @dob, getdate()) end

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-05-17 : 17:40:57

Actually, that wasn't entirely accurate either! Hopefully this is better:

select
case when month(getdate()) < month(@dob) or (month(getdate()) = month(@dob) and day(getdate()) < day(@dob)) then datediff(yy, @dob, getdate()) -1
else datediff(yy, @dob, getdate()) end

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-05-17 : 17:45:49

Found a more succinct syntax:

select case when datepart(dy, getdate()) < datepart(dy, @dob) then datediff(yy, @dob, getdate()) -1
else datediff(yy, @dob, getdate()) end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources