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!