Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jesse McLain » Best Way to Calculate Age
Add to briefcase
190 posts, Page 18 of 19
«««1516171819»»

Best Way to Calculate Age Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Nicole Bowman
Posted Monday, March 31, 2008 10:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:50 PM
Points: 171, Visits: 1,400
I agree with others here. Using my birth date the only one that gave me the correct answer was the second one and my birthday is 7 months away.

I want my point! :)

Nicole Bowman

Nothing is forever.
Post #477410
ephraims
Posted Tuesday, April 01, 2008 9:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 08, 2012 1:18 PM
Points: 66, Visits: 62
The second answer is the most accurate of the three; however, it does not account for leap years. The third answer would be the correct answer if the 1 and 0 were switched around.
Post #477782
aarif.pd
Posted Thursday, April 10, 2008 4:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 31, 2009 4:31 AM
Points: 5, Visits: 7
the second one is correct. give me my point.
Post #482895
Rick Harker
Posted Thursday, April 10, 2008 7:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 21, 2013 4:06 PM
Points: 263, Visits: 296
aarif.pd (4/10/2008)
the second one is correct. give me my point.


No, it is not. :)
Post #483042
john.arnott
Posted Thursday, April 10, 2008 6:26 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 10:57 PM
Points: 1,491, Visits: 3,008
Goodness, this one's gone on, hasn't it? And now I'm in it cuz of the row-size QOTD that had a naive age calculation thrown in.

Anyway, before reading through all 19 pages, I coded this:
Declare @DOB smallDatetime
Declare @Today smallDatetime
set @DoB = '02/29/2004'
set @Today = '2/29/08'

select	 @dob as Dob
		,@today as Today
		,year(@today)-year(@dob) 
		- case	when month(@today) < month(@dob)
				  or (month(@today) = month(@dob) and day(@today) < day(@dob))
				then 1
				else 0
		  end
		as Age

It turns out that it's pretty similar to some other solutions agreed to be correct, except that I used the functions "year()", "month()", and "day()" rather than "Datepart()".

Can someone who knows tell me why Datepart() would be preferrable? I like the distinct functions for readability.
Post #483366
Hugo Kornelis
Posted Friday, April 11, 2008 1:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:13 PM
Points: 5,237, Visits: 7,044
john.arnott (4/10/2008)
Can someone who knows tell me why Datepart() would be preferrable? I like the distinct functions for readability.


Hi John,

YEAR(...) is synonym for DATEPART(year, ...); similar for DAY and MONTH. From a technical point of view, there's no preference.

The only thing left is readability. Some, like you, prefer the shorter versions because they are shorter and hence easier to understand. Others prefer DATEPART because you can also use that to expose dateparts that have no synonym (like, for instance, week, minute, quarter) and they consider it more consistent to use the same function in all cases.

All in all, just a matter of personal preference. Though I'd recommend you to follow shop standards if there are any.

Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #483464
Robert Knights
Posted Saturday, May 31, 2008 6:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 20, 2011 1:21 AM
Points: 27, Visits: 139
Love all the trick questions in the QOD stuff - I never even looked past the first one as even though it may not always give the correct birthdate in English/American terms, it could be adjusted to more readably do so by flooring the other values out of the year! Hand up, I got it wrong, but then IMO not a great question since the actual "correct" answer doesn't work either.
Post #509470
Luc Philie
Posted Thursday, July 03, 2008 7:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, March 18, 2012 1:38 AM
Points: 26, Visits: 20
Declare @d smalldatetime
set @d = '1957/7/5'

Select DATEDIFF(yy, @d, GETDATE()) -
CASE WHEN
DATEPART(m, @d) < DATEPART(m, GETDATE())
OR (
DATEPART(m, @d) = DATEPART(m, GETDATE())
AND
DATEPART(d, @d) <= DATEPART(d, GETDATE())
) THEN 0 ELSE 1 END
Post #528451
Derek Dongray
Posted Friday, July 04, 2008 2:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:04 AM
Points: 1,342, Visits: 1,946
Luc Philie (7/3/2008)
Declare @d smalldatetime
set @d = '1957/7/5'

Select DATEDIFF(yy, @d, GETDATE()) -
CASE WHEN
DATEPART(m, @d) < DATEPART(m, GETDATE())
OR (
DATEPART(m, @d) = DATEPART(m, GETDATE())
AND
DATEPART(d, @d) <= DATEPART(d, GETDATE())
) THEN 0 ELSE 1 END
And your point is...?

Derek
Post #528543
Mayuresh
Posted Thursday, July 10, 2008 3:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 16, 2010 1:09 AM
Points: 218, Visits: 15
declare @DateOfBirth datetime
declare @result as varchar
set @DateOfBirth='1983-07-11'

select @result = (DATEDIFF(yy, @DateOfBirth, GETDATE()) -
case when Convert(Datetime,Convert(varchar,DATEPART(yy, GETDATE())) + '-' + Convert(varchar,DATEPART(m, @DateOfBirth))+ '-'+ Convert(varchar,DATEPART(d, @DateOfBirth)))
<=GetDate() THEN 0 ELSE 1 END )
select @Result

Here is the correct query to retrieve exact age.


MayurArt
Post #531443
« Prev Topic | Next Topic »

Add to briefcase
190 posts, Page 18 of 19
«««1516171819»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.