SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Calculating Age
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Trininole
Yak Posting Veteran

USA
83 Posts

Posted - 07/15/2009 :  15:42:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 07/15/2009 :  15:51:12  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 07/15/2009 :  18:07:35  Show Profile  Reply with Quote
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

Results:

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)




CODO ERGO SUM

Edited by - Michael Valentine Jones on 07/15/2009 18:23:33
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000