How rounding on DATEDIFF in Millisecond works?

66 Views Asked by At

I am using SQL Server 2022

Microsoft SQL Server 2022 (RTM-CU8-GDR) (KB5029503) - 16.0.4080.1 (X64) 
    Sep  1 2023 09:38:16 
    Copyright (C) 2022 Microsoft Corporation
    Developer Edition (64-bit) on Linux (Ubuntu 20.04.6 LTS) <X64>

I am trying to get better understanding of how datediff in millisecond works.

I tried the queries below, the 1st query turns out that it is rounding down. But then the 2nd query is rounding up.

1st Query:

select
    DATEDIFF(NANOSECOND, CAST('2023-12-20 23:00:12.460000000' AS DATETIME2), CAST('2023-12-20 23:00:12.506866700' AS DATETIME2)) callTimeInNano,
    DATEDIFF(MILLISECOND , CAST('2023-12-20 23:00:12.460000000' AS DATETIME2), CAST('2023-12-20 23:00:12.506866700' AS DATETIME2)) callTimeInMillis

Result:

+--------------+----------------+
|callTimeInNano|callTimeInMillis|
+--------------+----------------+
|46866700      |46              |
+--------------+----------------+

2nd Query:

select
    DATEDIFF(NANOSECOND , CAST('2023-12-20 23:00:12.826666700' AS DATETIME2), CAST('2023-12-20 23:00:12.903333300' AS DATETIME2)) callTimeInNano,
    DATEDIFF(MILLISECOND , CAST('2023-12-20 23:00:12.826666700' AS DATETIME2), CAST('2023-12-20 23:00:12.903333300' AS DATETIME2)) callTimeInMillis

Result:

+--------------+----------------+
|callTimeInNano|callTimeInMillis|
+--------------+----------------+
|76666600      |77              |
+--------------+----------------+

It is really weird when 46866700 -> 46, but 76666600 -> 77. Could anyone explain how it works?

Thanks!

0

There are 0 best solutions below