Post reply

Having trouble understanding the DateDiff command across year boundaries

  • June 23, 2010 at 8:55 am

    #223173

    I've been recently been doing a lot of datediff commands, and reading the books online it notes a flaw in the function.

    SELECT DATEDIFF(year, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000') returns 1.

    In this example, even though the timedifference is certainly not a year, because the year part of the date changes the function returns 1.

    I ran this in a server 2008 box and I validated the results.

    However, when I ran the exact same command in a 2005 box, it returned 0.

    So I have two questions:

    1) Is there a difference in this function between 2008 and 2005, or is it another configuration I should look for to determine the difference?

    2) Is the flaw noted above only for very small time intervals, or is the problem such that I should be doing a datediff on each part of the time (year,day,hour, etc) in order to truely get the difference? Right now the function seems very lackluster in its core functionality.

  • June 23, 2010 at 10:30 am

    #1184073

    Well, first of all, you can't run this exact same command in sql 2005... the microsecond precision is only available in the datetime2 datatype and other SQL 2008 new datatypes, and when running that command in sql 2005 you get a conversion error.

    However, running this command:

    SELECT DATEDIFF(year, '2005-12-31 23:59:59.997', '2006-01-01 00:00:00.000')

    in both environments does return a 1.

    Note that if to run in SQL 2005, you changed it to:

    SELECT DATEDIFF(year, '2005-12-31 23:59:59.999', '2006-01-01 00:00:00.000')

    (just removed the extra 9's) then that will round up to 2006-01-01, and the difference in years will be zero.

    Note that this is NOT a flaw in the function; instead it is doing what it is designed to do, and that is to report the number of BOUNDARIES crossed, not the DIFFERENCE.

    Since you started off with 2005, and ended with 2006, there is but one boundary crossed. What you are trying to get is the ELAPSED time, which is different. If you want elapsed time, you will have to calculate it yourself.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 23, 2010 at 5:25 pm

    #1184249

    Thank you for the answer. Sorry I got a little snide in that last post, wasn't the best day for me.

  • June 23, 2010 at 7:24 pm

    #1184268

    huston.dunlap (6/23/2010)

    Thank you for the answer. Sorry I got a little snide in that last post, wasn't the best day for me.

    So let's ask the real question... do you need something that will give an "age" in years properly? If so...

    DECLARE @Birthday DATETIME, @DateToCheck DATETIME

    SELECT @Birthday = '2005-12-31 23:59:59.997', @DateToCheck = '2006-01-01 00:00:00.000'

    SELECT YEAR(@DateToCheck - DATEPART(dy, @Birthday) + 1) - YEAR(@Birthday)

    --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.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

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

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