Alternatively, how did Microsoft make time travel possible?
Consider this code:
DECLARE @Offset datetimeoffset = sysdatetimeoffset(); DECLARE @UTC datetime = getUTCdate(); DECLARE @UTCFromOffset datetime = CONVERT(datetime,SWITCHOFFSET(@Offset,0)); SELECT Offset = @Offset, UTC = @UTC, UTCFromOffset = @UTCFromOffset, TimeTravelPossible = CASE WHEN @UTC < @UTCFromOffset THEN 1 ELSE 0 END;
@Offset is set before
@UTC, yet it sometimes has a later value. (I've tried this on SQL Server 2008 R2 and SQL Server 2016. You have to run it a few times to catch the suspect occurrences.)
This does not appear to be simply a matter of rounding or lack of precision. (In fact, I think the rounding is what "fixes" the issue occasionally.) The values for a sample run are as follows:
- 2017-06-07 12:01:58.8801139 -05:00
- 2017-06-07 17:01:58.877
- UTC From Offset:
- 2017-06-07 17:01:58.880
So datetime precision allows the .880 as a valid value.
Even Microsoft's GETUTCDATE examples show the SYS* values being later than the older methods, despite being SELECTed earlier:
SELECT 'SYSDATETIME() ', SYSDATETIME(); SELECT 'SYSDATETIMEOFFSET()', SYSDATETIMEOFFSET(); SELECT 'SYSUTCDATETIME() ', SYSUTCDATETIME(); SELECT 'CURRENT_TIMESTAMP ', CURRENT_TIMESTAMP; SELECT 'GETDATE() ', GETDATE(); SELECT 'GETUTCDATE() ', GETUTCDATE(); /* Returned: SYSDATETIME() 2007-05-03 18:34:11.9351421 SYSDATETIMEOFFSET() 2007-05-03 18:34:11.9351421 -07:00 SYSUTCDATETIME() 2007-05-04 01:34:11.9351421 CURRENT_TIMESTAMP 2007-05-03 18:34:11.933 GETDATE() 2007-05-03 18:34:11.933 GETUTCDATE() 2007-05-04 01:34:11.933 */
I presume this is because they come from different underlying system information. Can anyone confirm and provide details?
Microsoft's SYSDATETIMEOFFSET documentation says "SQL Server obtains the date and time values by using the GetSystemTimeAsFileTime() Windows API" (thanks srutzky), but their GETUTCDATE documentation is much less specific, saying only that the "value is derived from the operating system of the computer on which the instance of SQL Server is running".
(This isn't entirely academic. I ran into a minor issue caused by this. I was upgrading some procedures to use SYSDATETIMEOFFSET instead of GETUTCDATE, in hopes for greater precision in the future, but I started to get odd ordering because other procedures were still using GETUTCDATE and occasionally "jumping ahead" of my converted procedures in the logs.)