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
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Getting Age in SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Vivaldi
Constraint Violating Yak Guru

USA
298 Posts

Posted - 02/08/2001 :  14:29:15  Show Profile  Reply with Quote
Hello all.

I have been trying to find out if there is a simple
way to get someones age given their date of birth using SQL only.

I have the code in Javascript, but there are some major issues with javascript and dates.

I could code it in vbscript, but I don't like its reliability too
much either.

I know general sql,but I am not sure what I need to do
to have the age returned in a sql statement.

Anything to help is appreciated.

Sql Server 7, iis 4. nt 4



Software Composer

robvolk
Most Valuable Yak

USA
15637 Posts

Posted - 02/08/2001 :  17:19:54  Show Profile  Visit robvolk's Homepage  Reply with Quote
SELECT DateDiff(yy,@birthdate,getdate())

Go to Top of Page

Vivaldi
Constraint Violating Yak Guru

USA
298 Posts

Posted - 02/08/2001 :  17:35:15  Show Profile  Reply with Quote
quote:

SELECT DateDiff(yy,@birthdate,getdate())


RV, appreciate the time, but you know that that will give the incorrect age in years a lot of the time.

I am downright sure that I would have to put the code in an sp, given the logic, but I posted mostly too see if there was a t-sql function that I couldn't find.

Thanks for the time.




Software Composer
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 02/08/2001 :  18:05:32  Show Profile  Visit graz's Homepage  Reply with Quote
Can you post an example where it will give an incorrect number of years?

[url="http://www.SQLTeam.com"]SQLTeam.com[/url]
Go to Top of Page

Vivaldi
Constraint Violating Yak Guru

USA
298 Posts

Posted - 02/08/2001 :  18:39:33  Show Profile  Reply with Quote
quote:

Can you post an example where it will give an incorrect number of years?

[url="http://www.SQLTeam.com"]SQLTeam.com[/url]



Code to follow
<%
src = "Select DateDIFF(yy, '06/27/1977', '02/08/2001') as z"
Call CreateRecordset(qry)
Call OpenRecordset(qry,src)
if qry.bof and qry.eof then

else
response.write qry("z") & "<br>"
end if
Call DestroyRecordset(qry)
%>
Now since today is 2/8 and i my b-day happens to be the other.
it says i am 24, (i am not)

Appreciate the interest, but i am guessing its going to be
a vbscript function, oh well.



Software Composer
Go to Top of Page

robvolk
Most Valuable Yak

USA
15637 Posts

Posted - 02/08/2001 :  20:08:25  Show Profile  Visit robvolk's Homepage  Reply with Quote
Not so fast:

DECLARE @birthdate datetime, @other datetime
SELECT @birthdate='06/27/1977', @other='02/08/2001'
Select DateDIFF(yy,@birthdate,@other)-
CASE WHEN Str(datepart(mm,@birthdate),2,0)+
Str(datepart(dd,@birthdate),2,0)<=
Str(datepart(mm,@other),2,0)+Str(datepart(dd,@other),2,0) THEN 0
ELSE 1 END


Yes it's messy, and I think I can write it shorter, but IT WORKS! I wouldn't call the normal DateDiff() result wrong per se, but I wouldn't call it correct either.

Can't remember where I read it, or the exact words, but Joe Celko made a statement to the effect, "SQL can solve any problem that a procedural approach can solve", as far as data processing is concerned. I believe him. (but man, sometimes the procedural method is MUCH EASIER :)

Go to Top of Page

robvolk
Most Valuable Yak

USA
15637 Posts

Posted - 02/08/2001 :  21:01:12  Show Profile  Visit robvolk's Homepage  Reply with Quote
OK, here's the shorter version:

DECLARE @DOB datetime, @later datetime
SELECT @DOB='06/17/1977', @later='02/08/2001'
Select DateDIFF(yy,@DOB,@later)-
CASE WHEN @later>=DateAdd(yy,DateDIFF(yy,@DOB,@later), @DOB) THEN 0 ELSE 1 END AS Age


Books Online states that DateDiff() returns the # of boundaries (in this case, years) between two dates. Age isn't the same, it's a linear interval irrespective of calendar boundaries. To get around this, the formula above calculates your actual 24th birthday, and compares this to the later date. If later is greater (poetry!), then your 24th birthday has come and gone (ahhhh youth, how fleeting). Otherwise, your birthday hasn't come yet, and it subtracts a year from the DateDiff() value.

We should rename this post "Getting OLD in SQL" :)

Edited by - robvolk on 02/08/2001 21:07:09
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 02/08/2001 :  22:55:35  Show Profile  Visit graz's Homepage  Reply with Quote
robvolk you rock!

[url="http://www.SQLTeam.com"]SQLTeam.com[/url]
Go to Top of Page

Vivaldi
Constraint Violating Yak Guru

USA
298 Posts

Posted - 02/12/2001 :  09:21:51  Show Profile  Reply with Quote
RV,

I agree with Graz, You rock.

Your analogy of datediff is right on.
I was guessing there was a way to do it with pure sql, but I don't know the syntax too well.

Guess I got to smug about it too quickly.
I really appreciate the code and the effort.

Software Composer
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.06 seconds. Powered By: Snitz Forums 2000