Home Forums SQL Server 2008 T-SQL (SS2K8) datediff producing erroneous result when datetime is between midnight and 1AM RE: datediff producing erroneous result when datetime is between midnight and 1AM

  • Shifting gears back to the original subject... IF you're using the DATETIME datatype, you can really cheat on this one. As a bit of a sidebar, there's great utility in being able to do direct addition and subtraction of dates and times. I'll never understand why MS didn't include such a simple capability with the DATE and TIME datatypes. If they ever change that for the DATETIME datatype, there will be some high velocity porkchops in some designer's future. 😛

    Details are in the code below and I've created a test table in case folks want to do performance testing or "effective range" testing. Solutions with a DATEDIFF in milliseconds have their limits.

    --===== Create a million row test table of start and end dates

    -- where the end date is always later than the start date.

    -- In this case, there could be a full century of time between the dates.

    IF OBJECT_ID('tempdb..#JBMTest','U') IS NOT NULL DROP TABLE #JBMTest;

    WITH cteRandomStartDT AS

    (

    SELECT TOP 1000000

    StartDT = RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME)

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    )

    SELECT StartDT

    ,EndDT = RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2000','2100') + StartDT

    INTO #JBMTest

    FROM cteRandomStartDT

    ;

    --===== Calculate the Delta-T for each start/end date pair in the table.

    -- I'm dumping the output to a variable to take display times out of the picture.

    -- The formula calculates the total difference in time, converts that to HH:MI:SS:mmm format,

    -- and then stuffs the first 2 characters out and replaces them with difference in time

    -- measured in whole hours.

    DECLARE @Bitbucket VARCHAR(20);

    SET STATISTICS TIME ON;

    SELECT @Bitbucket = STUFF(CONVERT(VARCHAR(20),EndDT-StartDT,114),1,2,DATEDIFF(hh,0,EndDT-StartDT))

    FROM #JBMTest;

    SET STATISTICS TIME OFF;

    GO

    --===== Just to show that it really does work...

    SELECT *

    ,DeltaT = STUFF(CONVERT(VARCHAR(20),EndDT-StartDT,114),1,2,DATEDIFF(hh,0,EndDT-StartDT))

    FROM #JBMTest

    ORDER BY EndDT-StartDT

    ;

    --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)