0

I am puzzled by the following behaviour in SQL Server (tested on SQL Server 2017).

The goal is to return the number of seconds between two different datetime values. The two datetimes typically lie pretty close to another (differences measured between 0.4 and 2 seconds).

I started with the simplistic expression DATEDIFF(SECOND, startDateTime, endDateTime) and assumed that it might return 0 seconds for up to 499 (in fact due to precision 497) milliseconds and round up to 1 second for values starting at 500 milliseconds.

I read the MSDN Documentation but didn't get any wiser. It mentions something about "datepart boundaries" stating that "Those dates are adjacent and they differ in time by a hundred nanoseconds (.0000001 second). " I have milliseconds in difference. Does that also qualify as "being adjacent"?

However this is not how it works. It seams that if a boundary to the next minute is crossed the value of DATEDIFF(SECONDS...) always yields 1 while for the same interval all within one minute the seconds yield 0. Please have a look at rows with id 8 and 9 as well as 10 and 11 in the following result set:

enter image description here

Here's the code for the example:

WITH cteValues AS
(
    SELECT 
        id,
        CAST(startDateTime AS DATETIME) AS startDateTime,
        CAST(endDateTime AS DATETIME) AS endDateTime
    FROM
    (
        VALUES
        (   1,'20200202 23:56:19.737',  '20200202 23:56:21.723'),
        (   2,'20200202 23:56:25.690',  '20200202 23:56:27.297'),
        (   3,'20200202 23:56:31.453',  '20200202 23:56:32.753'),
        (   4,'20200202 23:56:36.940',  '20200202 23:56:37.533'),
        (   5,'20200202 23:56:37.353',  '20200202 23:56:37.927'),
        (   6,'20200202 23:56:38.653',  '20200202 23:56:39.213'),
        (   7,'20200202 23:56:42.630',  '20200202 23:56:43.163'),
        (   10,'20200203 06:34:50.900', '20200203 06:34:51.363'),
        (   8,'20200203 06:34:50.100',  '20200203 06:34:50.600'),
        (   9,'20200203 06:34:50.800',  '20200203 06:34:51.300'),
        (   11,'20200203 06:34:50.100', '20200203 06:34:50.563')
    ) t (id,startDateTime, endDateTime)
)
SELECT 
    id,
    startDateTime,
    endDateTime,
    CAST(endDateTime-startDateTime AS TIME) AS resultCast,
    DATEDIFF(SECOND, startDateTime, endDateTime) AS resultDatediffSeconds,
    ROUND(DATEDIFF(MILLISECOND, startDateTime, endDateTime) / 1000.0, 0) AS resultSecondsExtractedFromMilliseconds,
    DATEDIFF(MILLISECOND, startDateTime, endDateTime) AS resultDatediffMilliseconds
FROM
    cteValues
ORDER BY 
    resultDatediffMilliseconds DESC

Play for yourself in this SQL Fiddle.

Why is that so? It is astonishing that the same command with the MILLISECOND option returns a precise amount of seconds but with the SECOND option it does not.

Which way would you recommend to write the second calculation if one cares about accurately rounded results and is open for the best performing solution?

2

Think of DATEDIFF as a truncation and a subtraction.

First the given datetimes are truncated to whatever datepart is chosen. In your case this is SECONDS. So '20200203 06:34:50.100' becomes '20200203 06:34:50' and '20200203 06:34:50.600' becomes '20200203 06:34:50'. Subtract these and the difference is zero - there are zero second-sized time boundaries between the two given inputs.

Note that this is truncation and not rounding, and that the answer depends on the granularity of the datepart chosen. It can be confuing. The difference between '20191231 23:59:59' and '20200101 00:00:00' using YEAR, MONTH, DAY, HOUR, MINUTE and SECOND are all 1.

If you want to report to a precision of one tenth of a second you must calculate to the next most precise datepart (millisecond) and ROUND() the result.

1

Why is that so?

This phrase that you quote:

Those dates are adjacent and they differ in time by a hundred nanoseconds (.0000001 second).

refers to the sample data that the documentation uses to demonstrate that DATEDIFF returns a value of 1 when the difference between the start date and the end date cross one boundary. The fact that your data is of millisecond granularity doesn't have anything to do with "being adjacent".

DATEDIFF returns a count (as a signed integer value). It does not calculate the precise number of milliseconds between two dates and then convert, via either truncation or rounding, to seconds. Rather, it looks to see how many times you have to cross a second-boundary to get from startdate to enddate. It might help to think of a second-boundary as what you have to cross to get from <x>.999999999... seconds to <x+1>.000000000... seconds.

You can see the same behavior if you use minute for the datepart parameter. It doesn't count the number of seconds or milliseconds between the data and then truncate or round it. Rather, it counts the number of times you have to pass from 59.99999999... seconds of a given minute to 00.000000000... seconds of the next minute.

Which way would you recommend …?

It depends on your goal. As a thought experiment, ask yourself what you want the result to be when the two values are exactly 500 milliseconds apart. Do you want the result to be 1, or 0, or perhaps 0.5? Depending on your need, you can then use millisecond for the datepart parameter, divide by 1000, and then either round, truncate, or display it as-is.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.