Originally posted on stackexchange (https://dba.stackexchange.com/questions/287688/why-is-sysdatetimeoffset-precision-decreased-on-azure...), then on feedback.azure.com
(https://feedback.azure.com/forums/908035-sql-server/suggestions/43034130-sysdatetimeoffset-precisio...) until that was shut down, now here.
SELECT SYSDATETIMEOFFSET()
When I run the query on a local SQL Server 2019 instance, I get values like this:
2021-03-24 14:32:32.5811589 -05:00
2021-03-24 14:32:37.8169216 -05:00
2021-03-24 14:32:42.6465055 -05:00
When I run the query on an Azure SQL database, I get values like this:
2021-03-24 19:31:04.6733333 +00:00
2021-03-24 19:31:15.8300000 +00:00
2021-03-24 19:31:20.4866666 +00:00
In the latter, the last 5 digits are always repeating values. The value is less precise (or do I mean less accurate?) when the function runs on Azure SQL. Why is the behavior different?
The documentation for SYSDATETIMEOFFSET says:
SQL Server obtains the date and time values by using the GetSystemTimeAsFileTime() Windows API. The accuracy depends on the computer hardware and version of Windows on which the instance of SQL Server is running. The precision of this API is fixed at 100 nanoseconds. The accuracy can be determined by using the GetSystemTimeAdjustment() Windows API.
But if the hardware matters, I don't expect I'll be able to know that information for Azure SQL.
I am working around this issue by instead using this sort of query to get a more precise datetimeoffset value:
SELECT (SYSUTCDATETIME() AT TIME ZONE 'UTC')
For some reason SYSUTCDATETIME doesn't suffer from the "last 5 digits repeat" issue when I run it on Azure SQL. The documentation for SYSUTCDATETIME does say:
SYSDATETIME and SYSUTCDATETIME have more fractional seconds precision than GETDATE and GETUTCDATE.
but it doesn't mention having more precision than SYSDATETIMEOFFSET.
Either fix SYSDATETIMEOFFSET on Azure SQL or expand the documentation so that developers can know what precision can be relied upon from SYSDATETIMEOFFSET and SYSUTCDATETIME on Azure SQL.
