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

 Calculating Age Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, June 28, 2008 7:11 PM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 3:52 PM Points: 22,082, Visits: 28,979
Post #525559
 Posted Monday, June 30, 2008 4:06 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, February 20, 2012 1:34 AM Points: 4, Visits: 22
 You've identified one (of the two) most common ways to calculate age. The other one being the "I'm one year older if my birthday is today or was earlier this year" algorithm. See my blog (http://blog.tkbe.org/archive/python-how-old-are-you/) for code and discussion of the issues.
Post #525783
 Posted Monday, June 30, 2008 4:27 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, March 13, 2009 4:50 PM Points: 5, Visits: 11
 Hi everyone !I use this formula:Age = floor(datediff(day, [DateOfBirth], getdate())/(365.25))You can also put it in a calculated column.It works (I think) because one year actually has 365.25 days... Like we only count 365 days in a regular year, we need compensate with an extra day every four years (leap year).Try it and let me know how it goes !
Post #525792
 Posted Monday, June 30, 2008 4:53 AM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, November 20, 2013 7:43 AM Points: 1,419, Visits: 430
 This issue was heavily debated as a result of the errorous QotD a few months back...Several solutions was proposed, most of them partially correct, however the February 29. issue may be subject to different policies in different regions.The QotD can be found here: http://www.sqlservercentral.com/questions/T-SQL/62373/.
Post #525798
 Posted Monday, June 30, 2008 5:54 AM
 SSC Eights! Group: General Forum Members Last Login: Tuesday, October 01, 2013 10:40 AM Points: 985, Visits: 514
 Similar to Sergio's solution, mine breaks down an age into quarterly periods (three months), also accounting for leap year, so partial ages can be compared:ALTER FUNCTION [dbo].[fnCalcAge] (@DOB datetime, @CurrentDate datetime) RETURNS realASBEGIN RETURN FLOOR(((DATEDIFF(dd, @DOB, @CurrentDate) + CASE WHEN DATEPART(mm, @DOB) = DATEPART(mm, @CurrentDate) AND DATEPART(dd, @DOB) = DATEPART(dd, @CurrentDate) THEN 1 ELSE 0 END) / 365.25) / .25) * .25END "...when ye are in the service of your fellow beings ye are only in the service of your God." -- Mosiah 2:17
Post #525824
 Posted Monday, June 30, 2008 6:20 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, March 13, 2009 3:42 AM Points: 1, Visits: 12
 I have used the following function for some time in insurance database without issues. In this application, age is assumed to be a whole number in years (i.e. Never aged 5.2 years just 5 or 6). Simply put, you only reach your full age on the annivesary of your birthday.Create function dbo.GetAge(@BirthDate datetime, @CalcDate datetime)Returns intAsbegindeclare @theAge intIf Month(@CalcDate) > Month(@BirthDate ) select @theAge= (Year(@CalcDate) - Year(@BirthDate )) Else If Month(@CalcDate) < Month(@BirthDate ) select @theAge= ((Year(@CalcDate) - Year(@BirthDate )) - 1) Else If Day(@CalcDate) < Day(@BirthDate ) select @theAge= ((Year(@CalcDate) - Year(@BirthDate )) - 1) Else select @theAge= (Year(@CalcDate) - Year(@BirthDate )) return @theAgeend
Post #525848
 Posted Monday, June 30, 2008 7:24 AM
 SSC Journeyman Group: General Forum Members Last Login: Thursday, August 26, 2010 10:00 AM Points: 92, Visits: 47
 The final SQL didn't work for me, QA returned an error about the hyphen.Also, I'm not that experienced but surely it's "case when" and not "casewhen" ?
Post #525900
 Posted Monday, June 30, 2008 7:52 AM
 SSCrazy Group: General Forum Members Last Login: Today @ 6:43 AM Points: 2,378, Visits: 3,350
 http://www.sqlteam.com/article/datediff-function-demystified N 56°04'39.16"E 12°55'05.25"
Post #525929
 Posted Monday, June 30, 2008 8:12 AM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 3:52 PM Points: 22,082, Visits: 28,979
 To all those who are noticing some issues with some of the code in the article. I will contact Steve about making some corrections. The code in the original file submitted does not have the issues reported, so it may have been a "typesetting" issue as the article was reformatted for publication.My original code:`declare @dob datetime, @age int, @day datetimeset @day = '2008-02-28'set @dob = '2007-03-01'set @age = datediff(yy,@dob,@day) – case when @day < dateadd(yy, datediff(yy,@dob,@day), @dob) then 1 else 0 endselect @age`Please let me know if this code has a problem.
Post #525961
 Posted Monday, June 30, 2008 8:16 AM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 3:52 PM Points: 22,082, Visits: 28,979
 Peso (6/30/2008)http://www.sqlteam.com/article/datediff-function-demystifiedPeter,I just took the time to read your article above. I wish I had know about it as I was doing my article, I would have put a link to it in mine so that others could get more information about DATEDIFF and date arithmetic. Very well done and helpful.
Post #525965

 Permissions