How to calculate 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

General SQL Server Forums

New to SQL Server Programming

How to calculate age

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2010-02-24 : 04:35:42

How do I calculate someones age between a given DOB and GETDATE()

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-24 : 04:38:14

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

see the section on Finding Age

KH
[spoiler]Time is always against us[/spoiler]

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-24 : 04:40:26

SELECT DatePart(Year, GETDATE()) - DatePart(Year, DOB) - 1

Vabhav T

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-24 : 04:43:08

quote:
Originally posted by vaibhavktiwari83

SELECT DatePart(Year, GETDATE()) - DatePart(Year, DOB) - 1

Vabhav T

Why the "-1" ?

If the DOB is current year you will get -1

KH
[spoiler]Time is always against us[/spoiler]

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2010-02-24 : 04:44:23

I thought that just gives the years difference between the two dates, what if the difference is 8yrs 11 months?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-24 : 04:46:50

quote:
Originally posted by vaibhavktiwari83

SELECT DatePart(Year, GETDATE()) - DatePart(Year, DOB) - 1

Vabhav T

Dont directly rely on Datediff

SELECT datediff(year,'2009-12-31 23:59:59.997','2010-01-01 00:00:00.00')

Madhivanan

Failing to plan is Planning to fail

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2010-02-24 : 04:47:27

as in select datediff(yyyy,'01 Mar 1957',getdate())

= 53

when in fact the age should be 52 + 11 months!

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2010-02-24 : 04:48:26

oops, cross post, I'm looking for exact age, thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-24 : 04:49:39

find the difference in month then convert to years / months


SELECT	datediff(month, DOB, getdate()) / 12,
	datediff(month, DOB, getdate()) % 12

KH
[spoiler]Time is always against us[/spoiler]

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-24 : 04:50:44

Its a big topic of discussion i should not post this query directly.
because to show year months and days there will be different query

Vabhav T

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-02-24 : 04:53:22

quote:
Originally posted by Bill_C

I thought that just gives the years difference between the two dates, what if the difference is 8yrs 11 months?

SELECT datediff(month,dob,getdate())%12,
datediff(month,dob,getdate())/12 from table_name

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2010-02-24 : 05:12:59

Thanks all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources