Calculating Age

  • samsonjr2 (3/14/2009)


    I like to use computative column with datediff to calculate the age.

    Yes, its a shame you can't make it 'Persited' though, this means SQL has to recalc it with every query that includes that column.

  • Yes you are correct it recalcs.

  • rmattaway (3/13/2009)


    One method used by my employer is what we call the window of time. It was decided that everyone in our databases could not be older than 120 years. As such an Age Table was created with the number of days, weeks, months, years, etc. This assures that no matter what you can compute the days between using datediff then look up the age you want to use. This method assures that apples are truly compared with apples because someone that is 3652 days old is the same age as someone else 3652 days old.

    Most of the day-based solutions suffer from a fatal flaw:

    My birthdays are never 365.25 (or .242199, or .2425) days apart.

    They are either 365 or 366 days apart, with the number of days depending on:

    1. Leap Years (this year or last year)

    2. When I was born in the year: Before Feb 29th, on Feb 29th or after Feb 29th

    3. The rules for Leapling birthdays in non-Leap Years

    And for most real-world applications, being exact is important.

    I want to be able to drink, drive, receive benefits, and get paid more on my birthday.

    Approximations just aren't good enough 🙂

  • Tim,

    Exactly!

    Suppose the case of my friend, who is born on May 11th, 1993. On their 18th birthday, the system using 365.25 will declare them still 17 - which can get them into all kinds of trouble depending on how they choose to celebrate legal drinking age (in Australia).

    On the other hand, someone born on Feb 29th, 1992 will still be considered legally 17 on Feb 28th, 2010, and shouldn't be able to go drinking that day (or worse, be tried as an adult for some crime).

    This is why using the YYYYMMDD method works best.

    20110511 - 19930511 = 180000

    20100228 - 19920229 = 179999

    datediff(day,'19930511','20110511') / 365.25 = 17.998631

    And Lynn's method declares the leap-year person to be 18 on the 28th.

    Anyway - I think we all agree that 'age' can be awkward if the method for calculating isn't great.

    Rob

    Rob Farley
    LobsterPot Solutions & Adelaide SQL Server User Group
    Company: http://www.lobsterpot.com.au
    Blog: http://blogs.lobsterpot.com.au

  • 'works out the age of the apprentice.

    Dim total As String

    Dim strDOB As String = Format(CDate(.DateOfBirth), "MM dd")

    Dim todaysdate As String = Format(CDate(Date.Today), "MM dd")

    If strDOB > todaysdate Then

    strDOB = Format(CDate(.DateOfBirth), "yyyy") ''''''''''''''''''''''Hasn't had birthday yet this year

    todaysdate = Format(CDate(Date.Today), "yyyy")

    total = todaysdate - strDOB - 1

    Else

    strDOB = Format(CDate(.DateOfBirth), "yyyy") ''''''''''''''''''''''Has had birthday this year

    todaysdate = Format(CDate(Date.Today), "yyyy")

    total = todaysdate - strDOB

    End If

    p1_txtAge.Value = total

    p1_txtAge.Flatten = True

    this is something i wrote in VB.NET

    don't know if its any more accurate when working out the leap year issue

  • And Lynn's method declares the leap-year person to be 18 on the 28th.

    Yes, but I think someone made that point that legally in some jurisdictions, a person with a birthday of Feb 29 is legally older on Feb 28. This is inconvenient for IT people, but if it's a legal requirement, we must handle it. 🙂

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

  • ScottPletcher (3/16/2009)


    And Lynn's method declares the leap-year person to be 18 on the 28th.

    Yes, but I think someone made that point that legally in some jurisdictions, a person with a birthday of Feb 29 is legally older on Feb 28. This is inconvenient for IT people, but if it's a legal requirement, we must handle it. 🙂

    I also put a disclaimer in my article regarding leaplings. For purposes of my article I made a "business" decision to keep their birthday in February. Based on legal requirements, this could change, and we would then have to account for that in our calculations.

  • For calculating age in the form of x Years y Months z Days check the post of mine

    http://aspxdev.blogspot.com/2008/09/get-date-diference-in-form-of-x-years-y.html

  • This demonstrates that the datediff solution does not change the age at the time of the birthdate. All the age results are the same:

    declare @BirthDate DATETIME

    set @BirthDate = '08/27/1980'

    print @BirthDate

    print getdate()

    SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE())

    print 'TODAY IS THE TWENTY-SIXTH'

    set @BirthDate = '08/26/1980'

    print @BirthDate

    print getdate()

    SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE())

    set @BirthDate = '08/25/1980'

    print @BirthDate

    print getdate()

    SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE())

    set @BirthDate = '08/24/1980'

    print @BirthDate

    print getdate()

    SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE())

    The following translates the dates into YYYYMMDD and gets the correct age, changing at at the month and year of the birtdate.

    DECLARE

    @CURRENT_YEAR INTEGER

    , @CURRENT_MONTHS INTEGER

    , @CURRENT_DAYS INTEGER

    , @CURRENT_YYYYMMDD INTEGER

    , @AGE_INTEGER INTEGER

    SET @CURRENT_YEAR = DATEPART(YEAR, GETDATE())

    SET @CURRENT_MONTHS = DATEPART(MONTH, GETDATE())

    SET @CURRENT_DAYS = DATEPART(DAY, GETDATE())

    SET @CURRENT_YYYYMMDD = (@CURRENT_YEAR * 10000) + (@CURRENT_MONTHS * 100) + @CURRENT_DAYS

    SELECT 'CURRENT_DATE = ' , @CURRENT_YYYYMMDD

    declare @BirthDate DATETIME

    , @YEARS INTEGER

    , @MONTHS INTEGER

    , @DAYS INTEGER

    , @BIRTH_YYYYMMDD INTEGER

    set @BirthDate = '08/27/1980'

    set @YEARS = DATEPART(YEAR, @BIRTHDATE)

    SET @MONTHS = DATEPART(MM, @BIRTHDATE)

    SET @DAYS = DATEPART (DD, @BIRTHDATE)

    SET @BIRTH_YYYYMMDD = (@YEARS * 10000) + (@MONTHS * 100) + @DAYS

    SET @AGE_INTEGER = (@CURRENT_YYYYMMDD - @BIRTH_YYYYMMDD) / 10000

    SELECT 'BIRTH DATE = ' , @BIRTH_YYYYMMDD

    SELECT '@AGE_INTEGER = ' , @AGE_INTEGER

    PRINT 'TODAY IS THE TWENTY-SIXTH'

    set @BirthDate = '08/26/1980'

    set @YEARS = DATEPART(YEAR, @BIRTHDATE)

    SET @MONTHS = DATEPART(MM, @BIRTHDATE)

    SET @DAYS = DATEPART (DD, @BIRTHDATE)

    SET @BIRTH_YYYYMMDD = (@YEARS * 10000) + (@MONTHS * 100) + @DAYS

    SET @AGE_INTEGER = (@CURRENT_YYYYMMDD - @BIRTH_YYYYMMDD) / 10000

    SELECT 'BIRTH DATE = ' , @BIRTH_YYYYMMDD

    SELECT '@AGE_INTEGER = ' , @AGE_INTEGER

    set @BirthDate = '08/25/1980'

    set @YEARS = DATEPART(YEAR, @BIRTHDATE)

    SET @MONTHS = DATEPART(MM, @BIRTHDATE)

    SET @DAYS = DATEPART (DD, @BIRTHDATE)

    SET @BIRTH_YYYYMMDD = (@YEARS * 10000) + (@MONTHS * 100) + @DAYS

    SET @AGE_INTEGER = (@CURRENT_YYYYMMDD - @BIRTH_YYYYMMDD) / 10000

    SELECT 'BIRTH DATE = ' , @BIRTH_YYYYMMDD

    SELECT '@AGE_INTEGER = ' , @AGE_INTEGER

    set @BirthDate = '08/24/1980'

    set @YEARS = DATEPART(YEAR, @BIRTHDATE)

    SET @MONTHS = DATEPART(MM, @BIRTHDATE)

    SET @DAYS = DATEPART (DD, @BIRTHDATE)

    SET @BIRTH_YYYYMMDD = (@YEARS * 10000) + (@MONTHS * 100) + @DAYS

    SET @AGE_INTEGER = (@CURRENT_YYYYMMDD - @BIRTH_YYYYMMDD) / 10000

    SELECT 'BIRTH DATE = ' , @BIRTH_YYYYMMDD

    SELECT '@AGE_INTEGER = ' , @AGE_INTEGER

  • David. (8/27/2009)


    This demonstrates that the datediff solution does not change the age at the time of the birthdate. All the age results are the same:

    declare @BirthDate DATETIME

    set @BirthDate = '08/27/1980'

    print @BirthDate

    print getdate()

    SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE())

    print 'TODAY IS THE TWENTY-SIXTH'

    set @BirthDate = '08/26/1980'

    print @BirthDate

    print getdate()

    SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE())

    set @BirthDate = '08/25/1980'

    print @BirthDate

    print getdate()

    SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE())

    set @BirthDate = '08/24/1980'

    print @BirthDate

    print getdate()

    SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE())

    Your right, DATEDIFF(yy, @birthdate, getdate()) will return the same value regardless if the @birthdate is before on or after GETDATE(). That is the nature of the DATEDIFF function. TO actually calculate age requires additional calculations to make the final determination.

    Consider this, DATEDIFF(yy,'2008-12-31','2009-01-01') returns 1 as there is a difference in year periods is 1 even though there is actually only a difference of 1 day.

  • Here is another way I calculate age.

    SELECT @AGE = ' '+CAST(((datediff(ss, @DOB,GETDATE())) /31536000) AS NVARCHAR(MAX)) +

    ' Years, ' +

    CAST((((datediff(ss, @DOB,GETDATE())) %31536000) /2628000) AS NVARCHAR(MAX)) +

    ' Months, ' +

    CAST((((datediff(ss, @DOB,GETDATE())) %2628000) /86400) AS NVARCHAR(MAX)) +

    ' Days, ' +

    CAST((((datediff(ss, @DOB,GETDATE())) %86400) /3600) AS NVARCHAR(MAX)) +

    ' Hours, ' +

    CAST((((datediff(ss, @DOB,GETDATE())) %3600) /60) AS NVARCHAR(MAX)) +

    ' Minutes, ' +

    CAST(((datediff(ss, @DOB,GETDATE())) %60) AS NVARCHAR(MAX)) +

    ' Seconds '

  • /*

    Just use this function and get the exact age in "YYYMMDD" format (YearsMonthsDays)

    average days in a year: 365.2425

    average days ion a month: 30.436875

    USAGE: SELECT dbo.udf_CalcAge('1960-01-01', '2011-10-12')

    RESULT: "0510911" -- 51 YEARS, 9 MONTHS AND 11 DAYS

    */

    CREATE FUNCTION [dbo].[udf_CalcAge] (@StartDatedatetime = NULL, @EndDatedatetime = NULL)

    RETURNS varchar(7)

    WITH EXECUTE AS CALLER

    AS

    BEGIN

    RETURN (

    RIGHT('000' + CAST(FLOOR(DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) AS VARCHAR(3)), 3)+

    RIGHT('00' + CAST(FLOOR((DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) % 1 * 12) AS VARCHAR(2)), 2) +

    RIGHT('00' + CAST(FLOOR(ROUND(((DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) % 1 * 12) % 1 * 30.436875, 0)) AS VARCHAR(2)), 2)

    )

    END;

  • onecaring (10/12/2011)


    /*

    Just use this function and get the exact age in "YYYMMDD" format (YearsMonthsDays)

    average days in a year: 365.2425

    average days ion a month: 30.436875

    USAGE: SELECT dbo.udf_CalcAge('1960-01-01', '2011-10-12')

    RESULT: "0510911" -- 51 YEARS, 9 MONTHS AND 11 DAYS

    */

    CREATE FUNCTION [dbo].[udf_CalcAge] (@StartDatedatetime = NULL, @EndDatedatetime = NULL)

    RETURNS varchar(7)

    WITH EXECUTE AS CALLER

    AS

    BEGIN

    RETURN (

    RIGHT('000' + CAST(FLOOR(DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) AS VARCHAR(3)), 3)+

    RIGHT('00' + CAST(FLOOR((DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) % 1 * 12) AS VARCHAR(2)), 2) +

    RIGHT('00' + CAST(FLOOR(ROUND(((DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) % 1 * 12) % 1 * 30.436875, 0)) AS VARCHAR(2)), 2)

    )

    END;

    Interesting, if I set @StartDate = '1/1/2009' and @EndDate = '1/1/2010', I get 0001130, not one year. setting @EndDate = '1/2/2010' I get 0010001.

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • Interesting find. When does someone turn EXACTLY 1 year old? Or any full year old? Or when does 24 O'Clock happen? I guess it does happen but for such a nano second that it is almost impossible to get to that fraction. Or is it save to say never?

    One is either one this side of the line or on the other side but never ON the line when it comes to such precision like exactly one year old or exactly 24 O'Clock time.

    It is only my opinion... no one has to agree but positive criticism is most welcome 😀

  • onecaring (10/13/2011)


    Interesting find. When does someone turn EXACTLY 1 year old? Or any full year old? Or when does 24 O'Clock happen? I guess it does happen but for such a nano second that it is almost impossible to get to that fraction. Or is it save to say never?

    One is either one this side of the line or on the other side but never ON the line when it comes to such precision like exactly one year old or exactly 24 O'Clock time.

    It is only my opinion... no one has to agree but positive criticism is most welcome 😀

    With the precision of years/months/days, I'd say they're exactly one year old on their birthday.

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Viewing 15 posts - 76 through 90 (of 93 total)

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