Performing a Right and Comprehensive Age Calculation

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

    _____________
    Code for TallyGenerator

  • 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.

  • 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

  • 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.

  • 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.
    πŸ˜‰

    _____________
    Code for TallyGenerator

  • 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.

    _____________
    Code for TallyGenerator

  • 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.

    _____________
    Code for TallyGenerator

  • 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'

  • /*

    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]

    β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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

  • 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'

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

  • 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

  • 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.

    _____________
    Code for TallyGenerator

  • 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!

  • 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