## Calculating Age

 Author Message Lynn Pettis SSC Guru Group: General Forum Members Points: 303855 Visits: 41515 Bjorn Pettersen SSC-Addicted Group: General Forum Members Points: 420 Visits: 23 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. Sergio Lugo Valued Member Group: General Forum Members Points: 65 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 ! Øystein Fallo SSCommitted Group: General Forum Members Points: 1761 Visits: 454 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/. Aaron Cutshall SSCertifiable Group: General Forum Members Points: 6095 Visits: 1257 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 James Taylor-334331 SSC Journeyman Group: General Forum Members Points: 83 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 Sharon Matyk Say Hey Kid Group: General Forum Members Points: 684 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" ? SwePeso One Orange Chip Group: General Forum Members Points: 29049 Visits: 3434 http://www.sqlteam.com/article/datediff-function-demystified N 56°04'39.16"E 12°55'05.25" Lynn Pettis SSC Guru Group: General Forum Members Points: 303855 Visits: 41515 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. Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) Lynn Pettis SSC Guru Group: General Forum Members Points: 303855 Visits: 41515 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. Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)