Is there a way to obtain the current time as a datetime2(7) with full accuracy of 0.0000001 seconds?

Question

In SQL Server, datetime2(7) has a precision of 7 decimal places, e.g.:

2020-10-20 19:12:22.5294497
                    \_____/
                       |
              seven decimal places
               (100 ns precision)

This corresponds to the precision of the Windows FILETIME structure in Windows, which is based on a number of 100 ns ticks.

FILETIME counts the number of 100 ns ticks since 1601-01-01
datetime2(7) counts the number of 100 ns ticks since midnight

It has a precision of 100ns, so ideally it has an accuracy of 100 ns. And that you would be able to get two calls to SYSDATETIME to return:

2020-10-20 19:12:22.5294497
2020-10-20 19:12:22.5294498

Edit: Yes, ideally it will have an accuracy that matches its precision. This might not happen in reality - but ideally it does have matching accuracy.

So we try it

We setup a tight loop, and wait for the call to SYSDATETIME to return a value different from the previous call:

DECLARE @rc int = 0;

DECLARE @dt1 datetime2(7) = SYSUTCDATETIME();

WHILE @rc < 20
BEGIN
    DECLARE @dt2 datetime2(7) = SYSUTCDATETIME();

    --Next the next highest datetime value
    WHILE @dt1 = @dt2
    BEGIN
        --PRINT 'Incremeting datetime due to collision: '+CAST(@dt1 AS varchar(50))+' = '+CAST(@dt2 AS varchar(50))
        SET @dt2 = SYSUTCDATETIME();
    END
    PRINT CAST(@dt2 AS varchar(50))+'   (delta: '+CAST(DATEDIFF(ns, @dt1, @dt2) AS varchar(50))+' ns)'
    SET @rc = @rc+1;
    SET @dt1 = @dt2;
END

And when we run this, we see that it consistently takes 15,569,800 ns for SYSDATETIME to return a different value:

2020-10-20 19:12:21.4707033 (delta: 15569800 ns)
2020-10-20 19:12:21.4862731 (delta: 15569800 ns)
2020-10-20 19:12:21.5018429 (delta: 15569800 ns)
2020-10-20 19:12:21.5174127 (delta: 15569800 ns)
2020-10-20 19:12:21.5329825 (delta: 15569800 ns)
2020-10-20 19:12:21.5485523 (delta: 15569800 ns)
2020-10-20 19:12:21.5641221 (delta: 15569800 ns)
2020-10-20 19:12:21.5796919 (delta: 15569800 ns)
2020-10-20 19:12:21.5952617 (delta: 15569800 ns)
2020-10-20 19:12:21.6108315 (delta: 15569800 ns)
2020-10-20 19:12:21.6264013 (delta: 15569800 ns)
2020-10-20 19:12:21.6419711 (delta: 15569800 ns)
2020-10-20 19:12:21.6575409 (delta: 15569800 ns)
2020-10-20 19:12:21.6731107 (delta: 15569800 ns)
2020-10-20 19:12:21.6886805 (delta: 15569800 ns)
2020-10-20 19:12:21.7042503 (delta: 15569800 ns)
2020-10-20 19:12:21.7198201 (delta: 15569800 ns)
2020-10-20 19:12:21.7353899 (delta: 15569800 ns)
2020-10-20 19:12:21.7509597 (delta: 15569800 ns)
2020-10-20 19:12:21.7665295 (delta: 15569800 ns)

That accuracy is familiar:

15,569,800 ns
15,569.8 μs
15.5698 ms

We recognize it as a common hardware clock frequency - which limits the resolution of any clock to ~15 ms.

Which is why Microsoft created GetSystemTimePreciseAsFileTime, which allows getting the current time with an accuracy down to 100 ns.

Is there a way to obtain the current time as a datetime2 with a full accuracy of 0.0000001 seconds (100 ns)?

user_6748758 · Answer

The maximum precision datetime available is datetimeoffset(7). Per the Docs the time range offered is 00:00:00 through 23:59:59.9999999 and is accurate to 100 nanoseconds. You could create a table with a column of maximum precision something like this

drop table if exists #tTable;
go
create table #tTable(max_precision_dto   datetimeoffset(7));
insert #tTable(max_precision_dto) values (sysdatetimeoffset());

select * from #tTable;

Output

max_precision_dto
2020-10-20 20:06:34.6490280 +00:00

Is there a way to obtain the current time as a datetime2(7) with full accuracy of 0.0000001 seconds?

So we try it

1 Answers