Performing a Right and Comprehensive Age Calculation

  • Sergiy

    SSC Guru

    Points: 109851

    Comments posted to this topic are about the item Performing a Right and Comprehensive Age Calculation

  • Alex Burlan

    SSC Enthusiast

    Points: 106

    Great job, much appreciated!. Although, a small correction to your script.
    In the first part, with @BirthDate and @Today declared as DATETIME, the TallyGenerator for years should be called starting from zero.
    This way we will get a correct age calculation for small infants as well πŸ™‚ (age below 1 year won't be returned the way it's written now).

    Thank you for this article.

  • stephen.lightfoot

    Valued Member

    Points: 61

    I too have struggled with this in the past.  The easiest and fastest way I have found to do this is by using the function below.  It works every time, even for leap years and is only 1 line of code!

    CREATE FUNCTION [dbo].[fn_Age_Calc] (
     @DOB DATETIME
     ,@AsAtDate DATETIME
     )
    RETURNS INT
    AS
    BEGIN
     --Get the birthday for the year of the As At Date.
     --It basically works by converting the date into a number
     --in yyyymmdd format and that way there is a difference of
     --exactly 10000 between the same date of two different years. 
     --It even works with leap years.
     DECLARE @Age INT

     SET @Age = (0+Convert(Char(8),@AsAtDate,112) - Convert(Char(8),@DOB,112)) / 10000

     RETURN @Age
    END

  • Thomas Franz

    Hall of Fame

    Points: 3715

    Why do you not use a much more short and simple

    DECLARE @bd DATE = '19801231', @TD DATE = '20171231'
    SELECT DATEDIFF(YEAR, @bd, @TD) - CASE WHEN DATEPART(MONTH, @bd) * 100 + DATEPART(day, @bd)
                  > DATEPART(MONTH, @TD) * 100 + DATEPART(day, @TD)
                 THEN 1
                 ELSE 0
                END

    to calculate the age?

    Its the same as I do in real life - get the year diffence between today and birthdate and and subtract one more when he / she had not yet birthday in this year.

    God is real, unless declared integer.

  • Sergiy

    SSC Guru

    Points: 109851

    Alex Burlan - Tuesday, March 28, 2017 3:33 AM

    Great job, much appreciated!. Although, a small correction to your script.
    In the first part, with @BirthDate and @Today declared as DATETIME, the TallyGenerator for years should be called starting from zero.
    This way we will get a correct age calculation for small infants as well πŸ™‚ (age below 1 year won't be returned the way it's written now).

    Thank you for this article.

    Thanks.
    Well spotted.
    Funny enough, it's zero in the scripts for year and month calculations, and then I've got that "1" in the final script from somewhere.

    Must be a protection against mindless copy-paste users.
    πŸ˜‰

  • Sergiy

    SSC Guru

    Points: 109851

    t.franz - Tuesday, March 28, 2017 3:58 AM

    Why do you not use a much more short and simple

    DECLARE @bd DATE = '19801231', @TD DATE = '20171231'
    SELECT DATEDIFF(YEAR, @bd, @TD) - CASE WHEN DATEPART(MONTH, @bd) * 100 + DATEPART(day, @bd)
                  > DATEPART(MONTH, @TD) * 100 + DATEPART(day, @TD)
                 THEN 1
                 ELSE 0
                END

    to calculate the age?

    Its the same as I do in real life - get the year diffence between today and birthdate and and subtract one more when he / she had not yet birthday in this year.

    One of the obvious reasons "why" - lack of months and days of the age in the output.

  • Sergiy

    SSC Guru

    Points: 109851

    Sergiy - Tuesday, March 28, 2017 4:18 AM

    Alex Burlan - Tuesday, March 28, 2017 3:33 AM

    Great job, much appreciated!. Although, a small correction to your script.
    In the first part, with @BirthDate and @Today declared as DATETIME, the TallyGenerator for years should be called starting from zero.
    This way we will get a correct age calculation for small infants as well πŸ™‚ (age below 1 year won't be returned the way it's written now).

    Thank you for this article.

    Thanks.
    Well spotted.
    Funny enough, it's zero in the scripts for year and month calculations, and then I've got that "1" in the final script from somewhere.

    Must be a protection against mindless copy-paste users.
    πŸ˜‰

    I updated the attached script, published version should be refreshed some time soon.

  • Kev Riley

    SSCrazy Eights

    Points: 9033

    The first query in the script doesn't return any data for the following inputs

    DECLARE @BirthDate datetime, @Today DATETIME
    select @BirthDate='1976-02-29', @Today='2017-03-28'

    DECLARE @BirthDate datetime, @Today DATETIME
    select @BirthDate='1976-02-28', @Today='2017-03-28'

  • ChrisM@Work

    SSC Guru

    Points: 186120

    /*

    Assuming that today is 2017-03-16 (YYYY-MM-DD),

    then the Days value is incorrect for DOB's between2016-01-17 and 2016-01-30.

    Days = 16 for 2016-01-28, 2016-01-29, 2016-01-30 and2016-01-31.

    */

     

    DECLARE @Today DATETIME2 = GETDATE()-12

     

    ;WITH DOBs AS (

                    SELECTDOB = DATEADD(DAY,1-ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),CAST(@Today ASDATETIME2))

                    FROM

                                    (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d1 (n),

                                    (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d2 (n),

                                    (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d3 (n),

                                    (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d4 (n),

                                    (VALUES(0),(0),(0),(0),(0)) d5 (n)

    )

    SELECT d.DOB, Today = @Today, [AgeInYears],[AgeInMonths], [AgeInDays], z.*

    FROM DOBs d

    CROSS APPLY (

                    SELECT

                                    [AgeInDays]= CASE

                                                    WHENDAY(@Today) >= DAY(d.DOB) THEN DAY(@Today) - DAY(d.DOB)

                                                    ELSEDAY(@Today) + (DATEDIFF(DAY,d.DOB,EOMONTH(d.DOB)))

                                                    END,

                                    [AgeInYears]= (0 + CONVERT(CHAR(8),@Today,112) - CONVERT(CHAR(8),d.DOB,112)) / 10000

    ) x1

    CROSS APPLY (

                    SELECT[AgeInMonths] =DATEDIFF(month,DATEADD(YEAR,[AgeInYears],DATEADD(day,[AgeInDays],d.DOB)),@Today)

    ) y

    CROSS APPLY (

                    SELECTTOP 1 T3.Years, T3.Months, N [Days]

                                    --             , DATEADD(dd, N, T3.DateFrom)DateFrom, DateTo

                    FROMdbo.TallyGenerator(1,31, NULL, 1) tg2

                                    INNERJOIN (

                                                    SELECTTOP 1 T2.Years, tg2.N Months, DATEADD(mm, N, T2.EndOfLastWholeYear)EndOfLastWholeMonth, T2.DateTo

                                                    FROMdbo.TallyGenerator(1,12, NULL, 1) tg2

                                                                    INNERJOIN (

                                                                                                    SELECTTOP 1 tg1.N Years, DATEADD(YY, N, T1.DateFrom) EndOfLastWholeYear, T1.DateTo

                                                                                                    FROMdbo.TallyGenerator(1,DATEDIFF(YY, d.DOB, @Today)+1, NULL, 1) tg1

                                                                                                                    INNERJOIN (

                                                                                                                                                    SELECTd.DOB DateFrom, @Today DateTo

                                                                                                                                                    )T1 ON T1.DateTo >= DATEADD(YY, N, T1.DateFrom)

                                                                                                    ORDERBY N DESC

                                                                                                    )T2 ON T2.DateTo >= DATEADD(mm, N, T2.EndOfLastWholeYear)

                                                    ORDERBY N DESC

                                                    )T3 ON T3.DateTo >= DATEADD(dd, N, T3.EndOfLastWholeMonth)

                    ORDERBY N DESC

    ) z

    -- 44,299 / 00:00:01

    [/code]

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • andersen.bo

    Valued Member

    Points: 51

    kevriley - Tuesday, March 28, 2017 4:56 AM

    The first query in the script doesn't return any data for the following inputs

    DECLARE @BirthDate datetime, @Today DATETIME
    select @BirthDate='1976-02-29', @Today='2017-03-28'

    DECLARE @BirthDate datetime, @Today DATETIME
    select @BirthDate='1976-02-28', @Today='2017-03-28'

    I saw the same thing.. 
    select @BirthDate='1968-03-22', @Today='2017-03-28'

  • m2c2

    SSC Enthusiast

    Points: 116

    DATEDIFF(HOUR,DOB,GETDATE())/8766

  • andersen.bo

    Valued Member

    Points: 51

    you are missing the point.. the problem is not to know elapsed hours (or days or seconds) - it is to know years, months, days (and hours) correctly

  • Sergiy

    SSC Guru

    Points: 109851

    andersen.bo - Tuesday, March 28, 2017 8:06 AM

    kevriley - Tuesday, March 28, 2017 4:56 AM

    The first query in the script doesn't return any data for the following inputs

    DECLARE @BirthDate datetime, @Today DATETIME
    select @BirthDate='1976-02-29', @Today='2017-03-28'

    DECLARE @BirthDate datetime, @Today DATETIME
    select @BirthDate='1976-02-28', @Today='2017-03-28'

    I saw the same thing.. 
    select @BirthDate='1968-03-22', @Today='2017-03-28'

    Not sure I can understand what you are guys are talking about
    DECLARE @BirthDate datetime, @Today DATETIME
    select @BirthDate='2003-07-31', @Today='2008-07-30'
    select @BirthDate='1976-02-29', @Today='2017-03-28'
    select @BirthDate='1976-02-28', @Today='2017-03-28'
    select @BirthDate='1968-03-22', @Today='2017-03-28'
    SELECT TOP 1 T3.Years, T3.Months, N Days
        --    , DATEADD(dd, N, T3.DateFrom) DateFrom, DateTo
    FROM dbo.TallyGenerator(0, 31, NULL, 1) tg2
        INNER JOIN (
            SELECT TOP 1 T2.Years, tg2.N Months, DATEADD(mm, N, T2.EndOfLastWholeYear) EndOfLastWholeMonth, T2.DateTo
            FROM dbo.TallyGenerator(0, 12, NULL, 1) tg2
                INNER JOIN (
                        SELECT TOP 1 tg1.N Years, DATEADD(YY, N, T1.DateFrom) EndOfLastWholeYear, T1.DateTo
                        FROM dbo.TallyGenerator(0, DATEDIFF(YY, @BirthDate, @Today)+1, NULL, 1) tg1
                            INNER JOIN (
                                    SELECT @BirthDate DateFrom, @Today DateTo
                                    ) T1 ON T1.DateTo >= DATEADD(YY, N, T1.DateFrom)
                        ORDER BY N DESC
                        ) T2 ON T2.DateTo >= DATEADD(mm, N, T2.EndOfLastWholeYear)
            ORDER BY N DESC
            ) T3 ON T3.DateTo >= DATEADD(dd, N, T3.EndOfLastWholeMonth)
    ORDER BY N DESC
    go

    Works fine.

    Could it be because of that silly typo in TallyGenerator calls?
    FROM dbo.TallyGenerator(10, ...

    If yes - sorry about it.
    I fixed the attached script already, but  the update did not make it to the site just yet.

  • m2c2

    SSC Enthusiast

    Points: 116

    andersen.bo - Tuesday, March 28, 2017 3:21 PM

    you are missing the point.. the problem is not to know elapsed hours (or days or seconds) - it is to know years, months, days (and hours) correctly

    I've been calculating age using...DATEDIFF(HOUR,DOB,GETDATE())/8766...for a great many years, and it's never been wrong yet!

  • NBSteve

    Hall of Fame

    Points: 3462

    m2c2 - Tuesday, March 28, 2017 3:49 PM

    andersen.bo - Tuesday, March 28, 2017 3:21 PM

    you are missing the point.. the problem is not to know elapsed hours (or days or seconds) - it is to know years, months, days (and hours) correctly

    I've been calculating age using...DATEDIFF(HOUR,DOB,GETDATE())/8766...for a great many years, and it's never been wrong yet!

    Then you're lucky not to have a 5-year-old born on 3/28/2012, because they'll be the first to scream at you that DATEDIFF(HOUR,'2012-03-28','2017-03-28')/8766 is horribly wrong, and no, they're not 4 anymore, now they're a big 5 year old!  

    That being said, if you're required to report elapsed time in years, months, and days and you report it in years, you're still wrong no matter how accurate your answer may be.

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

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