Accurate Computation of Age

  • Simple way to compute your age accurately.

    A lot of query not exactly compute the age? yes! because sometime they only compute the datediff between DOB and datenow and divide it to 365.25 days and as a result they get a number with decimal something like 25 is the age with .06 decimal (age=25.06).

    In this query you exactly get the age as is it.

    Example 1

    DOB = 11/15/1987 and

    datenow =11/15/2012 the result would be

    AGE=25

    Example 2

    DOB = 11/14/1987 and

    datenow =11/15/2012 the result would be

    AGE=24

    SO HERE ARE THE QUERY

    DECLARE @DOB SMALLDATETIME

    SELECT @DOB = '11/15/1987'

    SELECT

    CASE

    WHEN MONTH(@DOB) <= MONTH(GETDATE()) AND DAY(@DOB) <=DAY(GETDATE()) THEN DATEDIFF(YY,@DOB,GETDATE())

    ELSE DATEDIFF(YY,@DOB,GETDATE())-1

    END AS AGE

    HOPE I CAN HELP! 🙂

  • Heres another take on it using FLOOR to strip of the decimal places.

    Declare @dob date = '14-nov-1987'

    Print @dob

    Print Datediff(D,@dob,getdate())

    Select Floor(Datediff(D,@dob,getdate()) / 365.25) Years

    , Floor(Datediff(D,@dob,getdate()) % 365.25) Days

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • It doesn't work correctly

    martinez.math (11/16/2012)


    Example 2

    DOB = 11/14/1987 and

    datenow =11/15/2012 the result would be

    AGE=24

    should be AGE=25

    but your code returns 24 (:

    p.s. It computes my age incorrect 🙁

  • Yes the result is 24 not 25 because the DOB is Not exactly the currentdate.

    but if the DOB is equal to currentdate or after the currentdate the result must be 25.:-)

    I review the query and this is working.

    Are you sure you get the right query?

    Below is the query.

    DECLARE @DOB SMALLDATETIME

    SELECT @DOB = '11/15/1987'

    SELECT

    CASE

    WHEN MONTH(@DOB) >= MONTH(GETDATE()) AND DAY(@DOB) >=DAY(GETDATE()) THEN DATEDIFF(YY,@DOB,GETDATE())

    ELSE DATEDIFF(YY,@DOB,GETDATE())-1

    END AS AGE

  • The key to finding age is to find the birthday for the current year, and subtract 1 from the difference in years if the current date is before the birthday this year.

    Note that the code below computes the current year birthday for Feb 29 birthdays as Feb 28 for non-leap years and Feb 29 for leap years. If you don't like that method, feel free to write your own.

    The concept of negative age doesn't seem to have any real world meaning, so it returns null if date of birth before current date.

    selecta.DOB,

    b.CurrDate,

    Age =

    datediff(yy,a.DOB,b.CurrDate) +

    case

    -- Age is null when DOB before current date

    when datediff(dd,a.DOB,b.CurrDate) < 0

    then null

    -- Subtract 1 if current date before birthday in current year

    when datediff(dd,dateadd(yy,datediff(yy,a.DOB,b.CurrDate),a.DOB),b.CurrDate) < 0

    then -1 else 0 end

    from

    ( --Test Date of Birth

    select DOB = convert(date,'19600229')union all

    select DOB = convert(date,'19521013')

    ) ajoin

    ( -- Test Current Dates

    select CurrDate = convert(date,'19001027')union all

    select CurrDate = convert(date,'20110227')union all

    select CurrDate = convert(date,'20110228')union all

    select CurrDate = convert(date,'20120228')union all

    select CurrDate = convert(date,'20120229')union all

    select CurrDate = convert(date,'20121012')union all

    select CurrDate = convert(date,'20121013')

    ) bon month(a.DOB) = month(b.CurrDate)

    order by

    a.DOB,

    b.CurrDate

    Results:

    DOB CurrDate Age

    ---------- ---------- -----------

    1952-10-13 1900-10-27 NULL

    1952-10-13 2012-10-12 59

    1952-10-13 2012-10-13 60

    1960-02-29 2011-02-27 50

    1960-02-29 2011-02-28 51

    1960-02-29 2012-02-28 51

    1960-02-29 2012-02-29 52

  • martinez.math (11/16/2012)


    Yes the result is 24 not 25 because the DOB is Not exactly the currentdate.

    but if the DOB is equal to currentdate or after the currentdate the result must be 25.:-)

    I review the query and this is working.

    Are you sure you get the right query?

    Below is the query.

    DECLARE @DOB SMALLDATETIME

    SELECT @DOB = '11/15/1987'

    SELECT

    CASE

    WHEN MONTH(@DOB) >= MONTH(GETDATE()) AND DAY(@DOB) >=DAY(GETDATE()) THEN DATEDIFF(YY,@DOB,GETDATE())

    ELSE DATEDIFF(YY,@DOB,GETDATE())-1

    END AS AGE

    Your code returns 60 when it should return 59 on the day before the 60th birthday.

    DECLARE @DOB SMALLDATETIME, @NOW SMALLDATETIME

    -- Get the day after 60 years before today

    select @DOB = dateadd(dd,+1,dateadd(yy,-60,getdate()))

    select [DOB] = @DOB, [NOW] = getdate()

    SELECT

    CASE

    WHEN MONTH(@DOB) >= MONTH(GETDATE()) AND DAY(@DOB) >=DAY(GETDATE()) THEN DATEDIFF(YY,@DOB,GETDATE())

    ELSE DATEDIFF(YY,@DOB,GETDATE())-1

    END AS AGE

    Results:

    DOB NOW

    ----------------------- -----------------------

    1952-11-18 02:58:00 2012-11-17 02:57:36.847

    AGE

    -----------

    60

Viewing 6 posts - 1 through 5 (of 5 total)

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