2

(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

1

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.

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.