Working Out Exact Age with DateDiff

  • Hi There,

    I am using the following in my SELECT Statement -

    DATEDIFF (year,dbo.[CORE_CO-PERSON].[D-O-B],GETDATE())AS Age,

    It is pulling me back the age - but it is not exact.

    For example I have a person born on 01-08-1977, however his age is being calculated as 32 when it should be 31.

    Is there anyway I can get the age to be more exact?

    Many Thanks

    Ryan

  • The logic for Age in this select statment shows a simple way to do it.

    declare @date_of_birth datetime

    set @date_of_birth = '19770801'

    select

    AGE =

    -- Find difference in years and subtract 1 if date is before this years birthday

    datediff(yy,@date_of_birth, a.date) -

    case when dateadd(yy,datediff(yy, @date_of_birth, a.DATE),@date_of_birth) > a.DATE

    then 1 else 0 end,

    DATE_OF_BIRTH = @date_of_birth,

    a.DATE

    from

    -- Test data from Date Table Function F_TABLE_DATE

    -- Function code available here:

    -- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

    F_TABLE_DATE('20090725','20090805') a

    order by

    a.DATE

    Results:

    AGE DATE_OF_BIRTH DATE

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

    31 1977-08-01 00:00:00.000 2009-07-25 00:00:00.000

    31 1977-08-01 00:00:00.000 2009-07-26 00:00:00.000

    31 1977-08-01 00:00:00.000 2009-07-27 00:00:00.000

    31 1977-08-01 00:00:00.000 2009-07-28 00:00:00.000

    31 1977-08-01 00:00:00.000 2009-07-29 00:00:00.000

    31 1977-08-01 00:00:00.000 2009-07-30 00:00:00.000

    31 1977-08-01 00:00:00.000 2009-07-31 00:00:00.000

    32 1977-08-01 00:00:00.000 2009-08-01 00:00:00.000

    32 1977-08-01 00:00:00.000 2009-08-02 00:00:00.000

    32 1977-08-01 00:00:00.000 2009-08-03 00:00:00.000

    32 1977-08-01 00:00:00.000 2009-08-04 00:00:00.000

    32 1977-08-01 00:00:00.000 2009-08-05 00:00:00.000

    (12 row(s) affected)

  • The reason is that you're subtracting years, and as Michael's logic shows, you need to account for where in the current year you are in relation to the date of birth.

  • If below is my currect Select, where would I input your soloution please?

    SELECT dbo.[CORE_CO-PERSON].[PERSON-REF]AS Person_Reference,

    dbo.[CORE_CO-PERSON].[PERSON-TITLE] AS Person_Title,

    dbo.[CORE_CO-PERSON].[INITIALS]AS Initials,

    dbo.[CORE_CO-PERSON].[FORENAMES] AS Forenames,

    dbo.[CORE_CO-PERSON].[SURNAME]AS Surname,

    dbo.[IH_RE-TENANCY].[corr-name1]AS Name_On_Tenancy1,

    dbo.[IH_RE-TENANCY].[corr-name2]AS Name_On_Tenancy2,

    dbo.[CORE_CO-PERSON].[D-O-B] AS DateOfBirth,

    DATEDIFF (year,dbo.[CORE_CO-PERSON].[D-O-B],GETDATE())AS Age,

    dbo.[CORE_CO-PERSON].[NHI-NO] AS NationalInsurranceNumber,

    dbo.[CORE_CO-PERSON].[ORIGIN-CODE]AS EthnicOrigin,

    dbo.[CORE_CO-PERSON].[GENDER]AS Gender,

    dbo.[IH_RE-TENANCY].[tenancy-ref]AS Tenancy_Referance,

    dbo.[IH_RE-TENANCY].[tncy-start]AS Tenancy_StartDate,

    dbo.[IH_RE-TNCY-PERSON].[ON-TNCY] AS On_The_Tenancy

    FROM dbo.[IH_RE-TENANCY]

    INNER JOINdbo.[IH_RE-TNCY-PERSON]

    ONdbo.[IH_RE-TENANCY].[TNCY-SYS-REF] = dbo.[IH_RE-TNCY-PERSON].[TNCY-SYS-REF]

    INNER JOINdbo.[CORE_CO-PERSON]

    ONdbo.[IH_RE-TNCY-PERSON].[PERSON-REF] = dbo.[CORE_CO-PERSON].[PERSON-REF]

    WHEREdbo.[IH_RE-TENANCY].[tncy-status] = 'CUR'

    ANDdbo.[IH_RE-TENANCY].[tenancy-ref] NOT LIKE 'FS%'

    ANDdbo.[IH_RE-TNCY-PERSON].[END-DATE]IS NULL

    --Need to only include NULL records for National Insurrance.

    --Only need to pull back those that are 16 and over.

  • Also datediff counts boundaries, not calendars

    try this

    select datediff(yy,'31dec2009','01jan2010')



    Clear Sky SQL
    My Blog[/url]

  • Michael Valentine Jones (7/20/2009)


    The logic for Age in this select statment shows a simple way to do it.

    declare @date_of_birth datetime

    set @date_of_birth = '19770801'

    select

    AGE =

    -- Find difference in years and subtract 1 if date is before this years birthday

    datediff(yy,@date_of_birth, a.date) -

    case when dateadd(yy,datediff(yy, @date_of_birth, a.DATE),@date_of_birth) > a.DATE

    then 1 else 0 end,

    DATE_OF_BIRTH = @date_of_birth,

    a.DATE

    from

    -- Test data from Date Table Function F_TABLE_DATE

    -- Function code available here:

    -- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

    F_TABLE_DATE('20090725','20090805') a

    order by

    a.DATE

    Results:

    AGE DATE_OF_BIRTH DATE

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

    31 1977-08-01 00:00:00.000 2009-07-25 00:00:00.000

    31 1977-08-01 00:00:00.000 2009-07-26 00:00:00.000

    31 1977-08-01 00:00:00.000 2009-07-27 00:00:00.000

    31 1977-08-01 00:00:00.000 2009-07-28 00:00:00.000

    31 1977-08-01 00:00:00.000 2009-07-29 00:00:00.000

    31 1977-08-01 00:00:00.000 2009-07-30 00:00:00.000

    31 1977-08-01 00:00:00.000 2009-07-31 00:00:00.000

    32 1977-08-01 00:00:00.000 2009-08-01 00:00:00.000

    32 1977-08-01 00:00:00.000 2009-08-02 00:00:00.000

    32 1977-08-01 00:00:00.000 2009-08-03 00:00:00.000

    32 1977-08-01 00:00:00.000 2009-08-04 00:00:00.000

    32 1977-08-01 00:00:00.000 2009-08-05 00:00:00.000

    (12 row(s) affected)

    You can also use datepart

    AGE =

    -- Find difference in years and subtract 1 if date is before this years birthday

    datediff(yy,@date_of_birth, a.date) -

    case when datepart(dy, @date_of_birth) > datepart(dy, a.DATE)

    then 1 else 0 end,

    or even this

    AGE =

    -- Find difference in years and subtract 1 if date is before this years birthday

    datediff(yy,@date_of_birth, a.date) -

    (sign(datepart(dy, @date_of_birth)-datepart(dy, a.DATE))+1)/2,

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • AGE =

    -- Find difference in years and subtract 1 if date is before this years birthday

    datediff(yy,@date_of_birth, a.date) -

    case when datepart(dy, @date_of_birth) > datepart(dy, a.DATE)

    then 1 else 0 end,

    Thank you for all that have replied. I still have no idea how to incorporate the above code into my current select statement (see below)

    SELECT dbo.[CORE_CO-PERSON].[PERSON-REF]AS Person_Reference,

    dbo.[CORE_CO-PERSON].[PERSON-TITLE] AS Person_Title,

    dbo.[CORE_CO-PERSON].[INITIALS]AS Initials,

    dbo.[CORE_CO-PERSON].[FORENAMES] AS Forenames,

    dbo.[CORE_CO-PERSON].[SURNAME]AS Surname,

    dbo.[IH_RE-TENANCY].[corr-name1]AS Name_On_Tenancy1,

    dbo.[IH_RE-TENANCY].[corr-name2]AS Name_On_Tenancy2,

    dbo.[CORE_CO-PERSON].[D-O-B] AS DateOfBirth,

    DATEDIFF (year,dbo.[CORE_CO-PERSON].[D-O-B],GETDATE())AS Age,

    dbo.[CORE_CO-PERSON].[NHI-NO] AS NationalInsurranceNumber,

    dbo.[CORE_CO-PERSON].[ORIGIN-CODE]AS EthnicOrigin,

    dbo.[CORE_CO-PERSON].[GENDER]AS Gender,

    dbo.[IH_RE-TENANCY].[tenancy-ref]AS Tenancy_Referance,

    dbo.[IH_RE-TENANCY].[tncy-start]AS Tenancy_StartDate,

    dbo.[IH_RE-TNCY-PERSON].[ON-TNCY] AS On_The_Tenancy

    FROM dbo.[IH_RE-TENANCY]

    INNER JOIN dbo.[IH_RE-TNCY-PERSON]

    ON dbo.[IH_RE-TENANCY].[TNCY-SYS-REF] = dbo.[IH_RE-TNCY-PERSON].[TNCY-SYS-REF]

    INNER JOIN dbo.[CORE_CO-PERSON]

    ON dbo.[IH_RE-TNCY-PERSON].[PERSON-REF] = dbo.[CORE_CO-PERSON].[PERSON-REF]

    WHERE dbo.[IH_RE-TENANCY].[tncy-status] = 'CUR'

    AND dbo.[IH_RE-TENANCY].[tenancy-ref] NOT LIKE 'FS%'

    AND dbo.[IH_RE-TNCY-PERSON].[END-DATE]IS NULL

    --Need to only include NULL records for National Insurrance.

    --Only need to pull back those that are 16 and over.

    I'm afraid I am a total novice when it comes to this, so sorry.

  • Ryan Keast (7/20/2009)


    AGE =

    -- Find difference in years and subtract 1 if date is before this years birthday

    datediff(yy,@date_of_birth, a.date) -

    case when datepart(dy, @date_of_birth) > datepart(dy, a.DATE)

    then 1 else 0 end,

    Thank you for all that have replied. I still have no idea how to incorporate the above code into my current select statement (see below)

    SELECT dbo.[CORE_CO-PERSON].[PERSON-REF]AS Person_Reference,

    dbo.[CORE_CO-PERSON].[PERSON-TITLE] AS Person_Title,

    dbo.[CORE_CO-PERSON].[INITIALS]AS Initials,

    dbo.[CORE_CO-PERSON].[FORENAMES] AS Forenames,

    dbo.[CORE_CO-PERSON].[SURNAME]AS Surname,

    dbo.[IH_RE-TENANCY].[corr-name1]AS Name_On_Tenancy1,

    dbo.[IH_RE-TENANCY].[corr-name2]AS Name_On_Tenancy2,

    dbo.[CORE_CO-PERSON].[D-O-B] AS DateOfBirth,

    DATEDIFF (year,dbo.[CORE_CO-PERSON].[D-O-B],GETDATE())AS Age,

    dbo.[CORE_CO-PERSON].[NHI-NO] AS NationalInsurranceNumber,

    dbo.[CORE_CO-PERSON].[ORIGIN-CODE]AS EthnicOrigin,

    dbo.[CORE_CO-PERSON].[GENDER]AS Gender,

    dbo.[IH_RE-TENANCY].[tenancy-ref]AS Tenancy_Referance,

    dbo.[IH_RE-TENANCY].[tncy-start]AS Tenancy_StartDate,

    dbo.[IH_RE-TNCY-PERSON].[ON-TNCY] AS On_The_Tenancy

    FROM dbo.[IH_RE-TENANCY]

    INNER JOIN dbo.[IH_RE-TNCY-PERSON]

    ON dbo.[IH_RE-TENANCY].[TNCY-SYS-REF] = dbo.[IH_RE-TNCY-PERSON].[TNCY-SYS-REF]

    INNER JOIN dbo.[CORE_CO-PERSON]

    ON dbo.[IH_RE-TNCY-PERSON].[PERSON-REF] = dbo.[CORE_CO-PERSON].[PERSON-REF]

    WHERE dbo.[IH_RE-TENANCY].[tncy-status] = 'CUR'

    AND dbo.[IH_RE-TENANCY].[tenancy-ref] NOT LIKE 'FS%'

    AND dbo.[IH_RE-TNCY-PERSON].[END-DATE]IS NULL

    --Need to only include NULL records for National Insurrance.

    --Only need to pull back those that are 16 and over.

    I'm afraid I am a total novice when it comes to this, so sorry.

    Add this to the WHERE clause

    AND DATEDIFF (year,dbo.[CORE_CO-PERSON].[D-O-B],GETDATE()) -

    CASE WHEN DATEPART(dy,dbo.[CORE_CO-PERSON].[D-O-B]) > DATEPART(dy, GETDATE()) THEN 1 ELSE 0 END >= 16

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • I also need to ask a question, what about leaplings? If some one is born on February 29th, when do you need to consider them a year older, on February 28th or March 1st? Based on this will actually determine how you calculate age.

  • Or

    WHERE

    ...

    and [CORE_CO-PERSON].[D-O-B] <= datediff(yy, -16, getdate()) -- where DOB is less than or equal to todays date 16 years ago.

    Edit:

    After 3 years, someone caught that I had the right idea but the wrong function in the code above. This is what I should have posted here:

    WHERE

    ...

    and [CORE_CO-PERSON].[D-O-B] <= dateadd(yy, -16, getdate()) -- where DOB is less than or equal to todays date 16 years ago.

  • Lynn Pettis (7/20/2009)


    I also need to ask a question, what about leaplings? If some one is born on February 29th, when do you need to consider them a year older, on February 28th or March 1st? Based on this will actually determine how you calculate age.

    Always a good question. There doesn't seem to be any universal agreement on this, so it will depend on the business requirements.

    The method I posted considers someone born on Feb 29 a year older on Feb 28 in non Leap years, and on Feb 29 in Leap years.

    The method posted by Mark (using DATAPART) considers someone born on Feb 29 a year older on Mar 1 in non Leap years, and on Feb 29 in Leap years.

  • Thanks guys. I will give this a go and let you know how I get on.

  • Try this

    DECLARE @FrmDate date = '31dec2009'

    ,@EndDate date = '01jan2010'

    SELECT

    CASE WHEN DATEpart(MM,@FrmDate)=DATEpart(MM,@EndDate)

    THEN CASE WHEN DATEpart(DD,@FrmDate)>DATEpart(DD,@EndDate)

    THEN datediff(MM,@FrmDate,@EndDate)/12 - 1

    ELSE datediff(MM,@FrmDate,@EndDate)/12

    END

    ELSE datediff(MM,@FrmDate,@EndDate)/12

    END

  • Lynn Pettis (7/20/2009)


    Or

    WHERE

    ...

    and [CORE_CO-PERSON].[D-O-B] <= datediff(yy, -16, getdate()) -- where DOB is less than or equal to todays date 16 years ago.

    Now THAT is clever, elegant and easy to understand. Love it!

    But it should actually be dateadd, not datediff, right?

    Rob Schripsema
    Propack, Inc.

  • Rob Schripsema (8/2/2012)


    Lynn Pettis (7/20/2009)


    Or

    WHERE

    ...

    and [CORE_CO-PERSON].[D-O-B] <= datediff(yy, -16, getdate()) -- where DOB is less than or equal to todays date 16 years ago.

    Now THAT is clever, elegant and easy to understand. Love it!

    But it should actually be dateadd, not datediff, right?

    You are correct. Took 3 years for someone to catch that mistake.

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

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