How to calculate the exact age ? Help


  • Select
    Datediff(yy, '20161201', '20171130')  as   age1 ,
    FLOOR(DATEDIFF(DAY, '20161201', '20171201') / 365.25)  as age2

    So the baby was born on 20161201
    Now, I want his age t appear 1 on 12-01-2017
    What SQl statement should I use ?

    if you run the above code, the output is wrong.

    1. The bay was exactly 1 on his birthday 12-01-2017 - but it says 0
    2. if you change the date in the FLOOR(.... to 20171202  you get 1 .. Ahhh but the baby should be one exactly on his birthday which is  20171201 !

  • Lynn Wins!
    Your formula even works for leap years..... 
    Have Fun


    declare @dob datetime,
       @age int,
       @day datetime

    set @dob = '2016-12-01'
    set @day = '2017-11-30'

    set @age = datediff(yy,@dob,@day) -
        case when @day < dateadd(yy,datediff(yy,@dob,@day), @dob) then 1 else 0 end
    select @age

    --BABY IS ZERO because we calculated his age one day before his birthday -- PERFECT

    Example 2

    BABY TURNED ONE .. even when she was born on a leap year ..

    declare @dob datetime,
       @age int,
       @day datetime

    set @dob = '2016-02-29'
    set @day = '2017-02-28'

    set @age = datediff(yy,@dob,@day) -
        case when @day < dateadd(yy,datediff(yy,@dob,@day), @dob) then 1 else 0 end
    select @age

    Conclusion:
    LYNN WINS!

Viewing 3 posts - 1 through 2 (of 2 total)

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