| Author |
 Topic  |
|
|
Vivaldi
Constraint Violating Yak Guru
USA
298 Posts |
 Posted - 02/08/2001 : 14:29:15
|
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
15568 Posts |
Posted - 02/08/2001 : 17:19:54
|
SELECT DateDiff(yy,@birthdate,getdate())
|
 |
|
|
Vivaldi
Constraint Violating Yak Guru
USA
298 Posts |
Posted - 02/08/2001 : 17:35:15
|
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 |
|
|
|
graz
Chief SQLTeam Crack Dealer
USA
4128 Posts |
Posted - 02/08/2001 : 18:05:32
|
Can you post an example where it will give an incorrect number of years?
[url="http://www.SQLTeam.com"]SQLTeam.com[/url] |
|
|
|
Vivaldi
Constraint Violating Yak Guru
USA
298 Posts |
Posted - 02/08/2001 : 18:39:33
|
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 |
|
|
|
robvolk
Most Valuable Yak
USA
15568 Posts |
Posted - 02/08/2001 : 20:08:25
|
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 :)
|
|
|
|
robvolk
Most Valuable Yak
USA
15568 Posts |
Posted - 02/08/2001 : 21:01:12
|
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 |
|
|
|
graz
Chief SQLTeam Crack Dealer
USA
4128 Posts |
|
|
Vivaldi
Constraint Violating Yak Guru
USA
298 Posts |
Posted - 02/12/2001 : 09:21:51
|
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 |
|
|
| |
Topic |
|
|
|
Getting Age in SQL
