Sql-server – T-SQL DATEDIFF_BIG – 469ms diff with currentmills and documented start-end constraints incorrect

datetimesql serversql-server-2017t-sql

(NB I don't count as a db admin… I'm just using one, so please assume zero other knowledge)

My reference date is '02/04/2017 18:00:00.124', and for no particular reason, whilst I need the time difference in milliseconds since some reference point, I picked ntp epoch of 1/1/1900 00:00.

I compare the result with the calculation of CurrentMills

SQL gives (code to follow) 3700144800124, Current Mills (with the extra 124ms added by hand) gives 3700144800593; the difference is 469ms.

Why is there a 469ms difference and why does DATEDIFF_BIG work at all when the documentation says it is limited to ~24 days?

I also did a comparison with Unix epoch conversion, respecting the stated limits (as shown) and the difference is also 469ms s CurrentMills.

Can someone explain these results?

SET DATEFORMAT dmy;  
GO  
DECLARE @datevar1 datetime2 = '02/04/2017 18:00:00.124';  
DECLARE @ntpEpochBase datetime2 = '01/01/1900 00:00:00.000'; 
SELECT DATEDIFF_BIG(millisecond, @ntpEpochBase, @datevar1);
GO
-- result 3700144800124
DECLARE @datevar1 datetime2 = '02/04/2017 18:00:00.124'; 
DECLARE @unixEpochBase datetime2 = '01/01/1970 00:00:00.000'; 
SELECT DATEDIFF_BIG(millisecond, @unixEpochBase, @datevar1);
GO
-- result 1491156000124
DECLARE @datevar1 datetime2 = '02/04/2017 18:00:00.124'; 
DECLARE @unixEpochBase datetime2 = '01/01/1970 00:00:00.000'; 
SELECT DATEDIFF_BIG(second, @unixEpochBase, '02/04/2017 00:00:00.000')*1000 + DATEDIFF_BIG(MILLISECOND,'02/04/2017 00:00:00.000','02/04/2017 18:00:00.124');
GO  
-- result 1491156000124

Has there been an (un)documented improvement in DATEDIFF_BIG capabiliites?

Setup is:

Microsoft Sequel Server Express (64 bit), v14.0.2002.14
Microsoft SQL Server Management Studio 14.0.17277.0 Microsoft
Analysis Services Client Tools 14.0.1016.262 Microsoft Data
Access Components (MDAC) 10.0.17134.1 Microsoft MSXML 3.0
6.0 Microsoft Internet Explorer 9.11.17134.0 Microsoft .NET Framework 4.0.30319.42000 Operating System 6.3.17134

Best Answer

The documentation for datediff_big is wrong. The limits specified is for datediff.

The millisecond value of 593 that you see is an artefact of the site CurrentMills that get the datetime value of now with millisecond precision and the keeps that value.

Use this link https://currentmillis.com/?now multiple times and navigate to 1970-01-01 and see that you get different rest values each time.