Post reply

DATEDIFF values are different for exactly same MiliSeconds

  • February 9, 2015 at 11:56 am

    #304008

    Hey SSC Members,

    Anyone know why the results I receiving for Seconds is different? I get the same MS results.

    Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)

    Jun 28 2012 08:36:30

    Copyright (c) Microsoft Corporation

    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

    --Returns 1 second

    SELECT RunTime_SEC = DATEDIFF(SECOND, '2015-02-09 13:34:13.977', '2015-02-09 13:34:14.230')

    ,RunTime_MS = DATEDIFF(ms, '2015-02-09 13:34:13.977', '2015-02-09 13:34:14.230')

    --Returns 0 second

    SELECT RunTime_SEC = DATEDIFF(SECOND, '2015-02-09 13:30:30.147', '2015-02-09 13:30:30.400')

    ,RunTime_MS = DATEDIFF(ms, '2015-02-09 13:30:30.147', '2015-02-09 13:30:30.400')

  • February 9, 2015 at 12:31 pm

    #1776475

    Because datediff doesn't care about smaller time parts, it just indicates the number of jumps.

    This is a common example:

    SELECT StartDate

    ,EndDate

    ,Years = DATEDIFF(YEAR, StartDate, EndDate)

    ,Days = DATEDIFF(Day, StartDate, EndDate)

    ,CaseDesc

    FROM(VALUES ('2014-12-31', '2015-01-01','1 Day 1 Year')

    ,('2014-01-01', '2015-12-31','729 Days 1 Year')

    ,('2015-01-01', '2015-12-31','364 Days 0 Years'))x(StartDate, EndDate, CaseDesc)

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • February 9, 2015 at 1:16 pm

    #1776482

    DATEDIFF does not compute the timespan and round it off to an integer number of the given time unit. It just counts how many boundaries of the given time unit appear in the date range. So a 23 millisecond span could also mean 1 year.

    SELECT RunTime_YEAR = DATEDIFF(YEAR, '2014-12-31 23:59:59.977', '2015-01-01 00:00:00.000')

    ,RunTime_MS = DATEDIFF(ms, '2014-12-31 23:59:59.977', '2015-01-01 00:00:00.000')

    [font="Courier New"]RunTime_YEAR = 1

    RunTime_MS = 23

    [/font]

  • February 9, 2015 at 1:45 pm

    #1776487

    Thank you Luis and Scott!

  • February 12, 2015 at 7:49 pm

    #1777391

    So the question that remains is... do you need it to? Are you actually trying to calculate and display a duration?

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

  • February 12, 2015 at 8:00 pm

    #1777393

    Hey Jeff,

    I created a script that will store statistics of a stored procedure which has a parameter. This parameter has 9k distinct values that can be used.

    So I created a loop calling the stored proc and stored the start and end date in a table

    I was querying this table for the results and most of the time finishes in milliseconds so used that, but I wanted to convert into seconds as well. So that is why I was confused when I saw script took 200 MS, but came up as 1 second using datediff(second,..). Now I understand that datediff is based on boundaries.

    Instead I just divided the milliseconds by 1000 and that gave me a good enough estimate.

  • February 13, 2015 at 5:43 pm

    #1777640

    brad.mason5 (2/12/2015)

    Hey Jeff,

    I created a script that will store statistics of a stored procedure which has a parameter. This parameter has 9k distinct values that can be used.

    So I created a loop calling the stored proc and stored the start and end date in a table

    I was querying this table for the results and most of the time finishes in milliseconds so used that, but I wanted to convert into seconds as well. So that is why I was confused when I saw script took 200 MS, but came up as 1 second using datediff(second,..). Now I understand that datediff is based on boundaries.

    Instead I just divided the milliseconds by 1000 and that gave me a good enough estimate.

    If your start and end dates are of the DATETIME datatype, just subtract one from the other and format it for display. See the following article for what I'm talking about.

    http://www.sqlservercentral.com/articles/T-SQL/103343/

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

Viewing 7 posts - 1 through 6 (of 6 total)

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