Datediff SECOND with DATETIME type and rounding returns questionable results 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:



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?
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.