Log in  ::  Register  ::  Not logged in

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

 Best Way to Calculate Age Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, March 31, 2008 10:38 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, November 8, 2016 4:15 PM Points: 189, Visits: 1,615
 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 BowmanNothing is forever.
Post #477410
 Posted Tuesday, April 1, 2008 9:27 AM
 Valued Member Group: General Forum Members Last Login: Wednesday, August 8, 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
 Posted Thursday, April 10, 2008 4:30 AM
 Forum 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
 Posted Thursday, April 10, 2008 7:58 AM
 SSC Veteran Group: General Forum Members Last Login: Sunday, November 27, 2016 6:20 AM Points: 281, Visits: 452
 aarif.pd (4/10/2008)the second one is correct. give me my point.No, it is not. :)
Post #483042
 Posted Thursday, April 10, 2008 6:26 PM
 Ten Centuries Group: General Forum Members Last Login: Monday, June 6, 2016 1:51 PM Points: 1,400, Visits: 3,059
 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 smallDatetimeDeclare @Today smallDatetimeset @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
 Posted Friday, April 11, 2008 1:14 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 9:30 AM Points: 7,765, Visits: 11,375
 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 MVPVisit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #483464
 Posted Saturday, May 31, 2008 6:52 AM
 SSC Rookie Group: General Forum Members Last Login: Tuesday, June 10, 2014 2:30 AM Points: 27, Visits: 142
 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
 Posted Thursday, July 3, 2008 7:31 PM
 SSC Rookie Group: General Forum Members Last Login: Sunday, March 18, 2012 1:38 AM Points: 26, Visits: 20
 Declare @d smalldatetimeset @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
 Posted Friday, July 4, 2008 2:42 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, February 22, 2016 8:32 AM Points: 1,274, Visits: 1,992
 Luc Philie (7/3/2008)Declare @d smalldatetimeset @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
 Posted Thursday, July 10, 2008 3:22 AM
 SSC Veteran Group: General Forum Members Last Login: Monday, February 8, 2016 9:51 PM Points: 218, Visits: 17
 declare @DateOfBirth datetimedeclare @result as varcharset @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 @ResultHere is the correct query to retrieve exact age. MayurArt
Post #531443

 Permissions

 Copyright © 2002-2016 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.