Calculating 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

Calculating Age

Author

Topic

Trininole
Yak Posting Veteran

83 Posts

Posted - 2009-07-15 : 15:42:35

I am using T-SQL and i am trying to calculate the user age from today's date and their date of birth. What syntax/statement such as "datediff" or "datepart" should i use? And should i use a case statement?

Roger DeFour

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-15 : 15:51:12

OK what in this thread is not clear to you?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=129341

No, you're never too old to Yak'n'Roll if you're too young to die.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-07-15 : 18:07:35

[code]declare @date_of_birth datetime
set @date_of_birth = '19490715'

select
AGE =
-- Find difference in years and subtract 1 if date is before this years birthday
datediff(yy,@date_of_birth, a.date) -
case when dateadd(yy,datediff(yy, @date_of_birth, a.DATE),@date_of_birth) > a.DATE then 1 else 0 end,
DATE_OF_BIRTH = @date_of_birth,
a.DATE
from
-- Test data from Date Table Function F_TABLE_DATE
-- Function code available here:
-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
F_TABLE_DATE('20090710','20090720') a
order by
a.DATE
[/code]
Results:
[code]
AGE DATE_OF_BIRTH DATE
----------- ----------------------- ------------------------
59 1949-07-15 00:00:00.000 2009-07-10 00:00:00.000
59 1949-07-15 00:00:00.000 2009-07-11 00:00:00.000
59 1949-07-15 00:00:00.000 2009-07-12 00:00:00.000
59 1949-07-15 00:00:00.000 2009-07-13 00:00:00.000
59 1949-07-15 00:00:00.000 2009-07-14 00:00:00.000
60 1949-07-15 00:00:00.000 2009-07-15 00:00:00.000
60 1949-07-15 00:00:00.000 2009-07-16 00:00:00.000
60 1949-07-15 00:00:00.000 2009-07-17 00:00:00.000
60 1949-07-15 00:00:00.000 2009-07-18 00:00:00.000
60 1949-07-15 00:00:00.000 2009-07-19 00:00:00.000
60 1949-07-15 00:00:00.000 2009-07-20 00:00:00.000

(11 row(s) affected)

[/code]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources