Avoiding Unexpected Results with DATEDIFF in Year Comparisons

  • DBA4482

    SSC Journeyman

    Points: 77

    Comments posted to this topic are about the item Avoiding Unexpected Results with DATEDIFF in Year Comparisons

  • Lynn Pettis

    SSC Guru

    Points: 442167

    Here is a way to make the calculation:

    declare @testdate datetime = '2013-10-24';
    select
     getdate() as Today
     , @testdate as DueDate
     , datediff(year,@testdate,getdate()) -
       case when getdate() < dateadd(year,datediff(year,@testdate,getdate()),@testdate)
            then 1
         else 0
          end as DiffInYears;
    go

  • Jeff Moden

    SSC Guru

    Points: 995116

    @dcs1548 ,

    Dividing by 365 is far from accurate, as well, and there's no reason to suffer such inaccuracies especially on date ranges that include one or more leap years.


     CREATE FUNCTION dbo.AgeInYears
    /**********************************************************************************
     Purpose:
     Given two dates in expected order, calculate the difference in the dates by years.
    -----------------------------------------------------------------------------------
     Programmers Notes:
     1. This is a high performance iTVF (Inline Table Valued Function), which is faster
        than an equivalent Scalar Function and just as fast as inline code. Please
        refer to the following link for proof of that.
        http://www.sqlservercentral.com/articles/T-SQL/91724/
     2. When the output is dumped to a variable to take disk and screen performance
        out of the picture, it executes a million row table of two dates in ~820ms.
    -----------------------------------------------------------------------------------
     Usage Examples:
    --===== Basic syntax
     SELECT AgeInYears
       FROM dbo.AgeInYears(@pLoDate,@pHiDate)
    ;
    --===== Use against a table where the end date may be NULL
     SELECT AgeInYears
       FROM dbo.SomeTable st
      CROSS APPLY dbo.AgeInYears(st.SomeDate1,ISNULL(st.SomeDate2,GETDATE())
    ;
    -----------------------------------------------------------------------------------
     Revision History:
     Rev 00 - 11 Mar 2017 - Group effort by the folks at the following link.
            - https://www.sqlservercentral.com/Forums/1863521/
            - Formalized code - Jeff Moden
    **********************************************************************************/
    --===== Declare the I/O for this function
            (--======= These dates must be in the expected order.
             @pLoDate DATE
            ,@pHiDate DATE
            )
    RETURNS TABLE WITH SCHEMABINDING AS
     RETURN
     SELECT AgeInYears =
                DATEDIFF(yy,@pLoDate,@pHiDate)
              - CASE
                    WHEN CONVERT(CHAR(5),@pLoDate,1) > CONVERT(CHAR(5),@pHiDate,1)
                    THEN 1
                    ELSE 0
                END
    ;

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • DaveBoltman

    SSC Enthusiast

    Points: 180

    There aren't 365 days in a year. Seeing that hard-coded constant in the code raised about seventeen code-smells in my mind

  • smw147

    Ten Centuries

    Points: 1387

    I use this code to work out Age
    DECLARE @DateOfBirth DATETIME = '1980-07-12';
    SELECT
     DATEDIFF(YEAR, @DateOfBirth, GETDATE())
     - CASE
      WHEN (MONTH(@DateOfBirth) > MONTH(GETDATE()))
        OR (MONTH(@DateOfBirth) = MONTH(GETDATE()) AND DAY(@DateOfBirth) > DAY (GETDATE()))
       THEN 1
       ELSE 0
      END AS Age

    or I use this function if being used in a wider project

    IF object_id(N'[dbo].[udf_CalculateAge]', 'Fn') IS NOT NULL
        DROP FUNCTION [DBO].[udf_CalculateAge]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE FUNCTION udf_CalculateAge(@dateofbirth AS DATE, @clientid AS INT) RETURNS INT
    AS
    BEGIN

    DECLARE @output AS INT

    SELECT @output=
        DATEDIFF(yyyy, @dateofbirth, GETDATE())
            - CASE
                WHEN MONTH(@dateofbirth) > MONTH(GETDATE()) THEN 1
                WHEN MONTH(@dateofbirth) = MONTH(GETDATE()) AND DAY(@dateofbirth) > DAY(GETDATE()) THEN 1
                ELSE 0
            END
    FROM Clients
    WHERE ClientID = @clientid

    RETURN @output;
    END
    GO

    Regards

    Steve

  • Jeff Moden

    SSC Guru

    Points: 995116

    DaveBoltman - Monday, April 9, 2018 4:08 AM

    There aren't 365 days in a year. Seeing that hard-coded constant in the code raised about seventeen code-smells in my mind

    You should post what they are along with some code to resolve the issue. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Thom A

    SSC Guru

    Points: 98447

    On a different nit-pick to what has already been discussed, it's helpful to post your code as text, not as an image. Other users can't highlight the text within your image and use it, thus they would have to type it all back out. For a small script as this, that's not awful, however, for longer code that's going to drive people away from using your solution (or have them type it out incorrectly).

    Of course, however, this is a kind of mute point when several others have pointed out a limitation/flaw of the script. 🙂

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • SQLOzzie

    Ten Centuries

    Points: 1372

    I recall back in my engineering school days we always used 365.25 to account for leap year.

    SQLOzzie

  • Eirikur Eiriksson

    SSC Guru

    Points: 182367

    For fun, here is a function that calculates the number of days in a year (leap year or normal year)
    😎

    USE TEEST;
    GO
    SET NOCOUNT ON;
    GO
    CREATE FUNCTION dbo.ITVFN_GET_DAYS_IN_YEAR
    ---------------------------------------------------------------------
    -- Calculate the number of days in a year
    -- The function is based on the common leap year algorithm
    ---------------------------------------------------------------------
    --  if (year is not divisible by 4) then (it is a common year)
    -- else if (year is not divisible by 100) then (it is a leap year)
    -- else if (year is not divisible by 400) then (it is a common year)
    -- else (it is a leap year)
    -- © Wikipedia
    -- https://en.wikipedia.org/wiki/Leap_year
    ---------------------------------------------------------------------
    (
      @INPUT_DATE DATETIME
    )
    RETURNS TABLE WITH SCHEMABINDING
    AS RETURN
    WITH THE_YEAR AS
    (
      SELECT YEAR(@INPUT_DATE) AS YEAR_NUM
    )
    SELECT
      365 + SIGN( 1 - SIGN(((TY.YEAR_NUM) % 4 )) + (SIGN(((TY.YEAR_NUM) % 100)) - SIGN(((TY.YEAR_NUM) % 400)))) AS DAYS_IN_YEAR
    FROM  THE_YEAR  TY;
    GO

  • trevor.adams

    Default port

    Points: 1449

    So the coping strategies are

    1) A function such as (advantage - it will be correct - disadvantage - will be slow if needed for lots of data)

    CREATE FUNCTION [dbo].[UDF_CalculateAge]

    (

    @BirthDate DATETIME,

    @CurrentDate DATETIME

    )

    RETURNS INT

    AS

    BEGIN

    IF @BirthDate > @CurrentDate

    RETURN 0

    DECLARE @Age INT

    SELECT @Age = DATEDIFF(YY, @BirthDate, @CurrentDate) - CASE WHEN( (MONTH(@BirthDate)*100 + DAY(@BirthDate)) > (MONTH(@CurrentDate)*100 + DAY(@CurrentDate)) ) THEN 1 ELSE 0 END

    RETURN @Age

    END

    2) Do a datediff in days and divide the result by 365.25 (advantage - it will be quick , disadvantage - it will sometimes be a little bit wrong but generally only for less than a day - this might be good enough)
    3) It is sometimes appropriate to do a dateadd instead of datediff eg

    SELECT top 10 DATE_OF_BIRTH,

    CASE WHEN DATEADD(yy,18,DATE_OF_BIRTH) > GETDATE() THEN 'Child'

    ELSE 'Adult'

    END AS AdultorChild

    FROM dimpatient

  • GeorgeCopeland

    SSCertifiable

    Points: 6896

    What I think is important to note about the author's post is this: When you do your own mathematical manipulation of datetime data, you are probably--almost certainly--making a mistake. A datetime is a data structure, not a numeral. For all date calculations, I highly advise using only functions that are provided for the datatype. For example, note Jeff's AgeInYears function, which uses DateDiff and Convert.

  • SQLNoob91

    Grasshopper

    Points: 23

    You would actually want to divide by 365.25, not 365.0, if you want a more accurate year difference.

  • Jeff Moden

    SSC Guru

    Points: 995116

    zechstarks - Monday, April 9, 2018 9:02 AM

    You would actually want to divide by 365.25, not 365.0, if you want a more accurate year difference.

    Gosh, no.  Don't use division for this.  The alternatives are easy and guaranteed to be accurate.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 995116

    trevor.adams - Monday, April 9, 2018 7:38 AM

    So the coping strategies are

    1) A function such as (advantage - it will be correct - disadvantage - will be slow if needed for lots of data)

    CREATE FUNCTION [dbo].[UDF_CalculateAge]

    (

    @BirthDate DATETIME,

    @CurrentDate DATETIME

    )

    RETURNS INT

    AS

    BEGIN

    IF @BirthDate > @CurrentDate

    RETURN 0

    DECLARE @Age INT

    SELECT @Age = DATEDIFF(YY, @BirthDate, @CurrentDate) - CASE WHEN( (MONTH(@BirthDate)*100 + DAY(@BirthDate)) > (MONTH(@CurrentDate)*100 + DAY(@CurrentDate)) ) THEN 1 ELSE 0 END

    RETURN @Age

    END

    2) Do a datediff in days and divide the result by 365.25 (advantage - it will be quick , disadvantage - it will sometimes be a little bit wrong but generally only for less than a day - this might be good enough)
    3) It is sometimes appropriate to do a dateadd instead of datediff eg

    SELECT top 10 DATE_OF_BIRTH,

    CASE WHEN DATEADD(yy,18,DATE_OF_BIRTH) > GETDATE() THEN 'Child'

    ELSE 'Adult'

    END AS AdultorChild

    FROM dimpatient

    "Good enough" usually isn't, even if someone thinks so. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • kevin 19285

    Valued Member

    Points: 63

    Here's mine...
    CREATE FUNCTION YearsDiff(@Born date,@AsOf date) RETURNS int AS
    BEGIN
        DECLARE @y int
        SET @y = DATEDIFF(YEAR,@Born,@AsOf)
        IF DATEADD(year,@y,@Born) > @AsOf SET @y -= 1
        RETURN @y
    END

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

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