Calculating Age

  • Lynn Pettis

    SSC Guru

    Points: 442360

    Comments posted to this topic are about the item Calculating Age

  • Bjorn Pettersen

    SSC-Addicted

    Points: 474

    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

    Old Hand

    Points: 302

    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

    Points: 1835

    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 N. Cutshall

    SSCrazy Eights

    Points: 8815

    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 real

    AS

    BEGIN

    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) * .25

    END

  • James Taylor-334331

    SSC Journeyman

    Points: 95

    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 int

    As

    begin

    declare @theAge int

    If 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 @theAge

    end

  • Sharon Matyk

    SSC Eights!

    Points: 872

    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

    SSC-Dedicated

    Points: 39693

    http://www.sqlteam.com/article/datediff-function-demystified


    N 56°04'39.16"
    E 12°55'05.25"

  • Lynn Pettis

    SSC Guru

    Points: 442360

    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 datetime

    set @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 end

    select @age

    Please let me know if this code has a problem.

    😎

  • Lynn Pettis

    SSC Guru

    Points: 442360

    Peter,

    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.

    😎

  • mkeltz

    SSC Enthusiast

    Points: 121

    I got this error from that code:

    Msg 102, Level 15, State 1, Line 7

    Incorrect syntax near '–'.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720983

    Try deleting the hyphen and re-entering it. Sometimes converting from SSMS to Word to our editor and HTML gets strange replacements for characters. I think this was one of those cases.

  • Peggy Rowles-487964

    SSC Enthusiast

    Points: 149

    The posted codes work fine for full years, but I have been trying to find good SQL code that will calculate down to days if needed (for babies). I a baby is under a couple weeks old, I need to report how many days old they are. If the baby is older than that, but under a few months old, I need to report how many weeks old they are. If the child is older than a few months, but less than a year, I have to report how many months old they are. I haven't been able yet to come up with code that will do that and display something like (as applicable):

    3d or

    2w or

    4m or (if older)

    16y

  • Lynn Pettis

    SSC Guru

    Points: 442360

    Peggy Rowles (6/30/2008)


    The posted codes work fine for full years, but I have been trying to find good SQL code that will calculate down to days if needed (for babies). I a baby is under a couple weeks old, I need to report how many days old they are. If the baby is older than that, but under a few months old, I need to report how many weeks old they are. If the child is older than a few months, but less than a year, I have to report how many months old they are. I haven't been able yet to come up with code that will do that and display something like (as applicable):

    3d or

    2w or

    4m or (if older)

    16y

    Your requirements above a slightly vague. Could you provide firmer requirements on what needs to be reported, and how you would want the data returned?

    😎

  • mkeltz

    SSC Enthusiast

    Points: 121

    That worked! thanks!

Viewing 15 posts - 1 through 15 (of 94 total)

You must be logged in to reply to this topic. Login to reply