Verify Time Precision in SQL Server Transact-SQL


How to verify the precession of the server system time in SQL Server Transact-SQL?



You can run a WHILE loop to check the precision of server system time in Transact-SQL as shown below:

DECLARE @count INT = 0;
WHILE @count<100 BEGIN
  SET @count = @count + 1;
  PRINT CAST(@count AS CHAR(3))+': '
     +CAST(CAST(@var AS TIME(7)) AS CHAR(16));
1  : 19:39:22.3348095
2  : 19:39:22.3348095
13 : 19:39:22.3348095

14 : 19:39:22.3358096
15 : 19:39:22.3358096
78 : 19:39:22.3358096

79 : 19:39:22.3968131
80 : 19:39:22.3968131
100: 19:39:22.3968131

As you can see from the output, the actual precision of the SYSDATETIME() function is very poor, about 0.001 second. This is much lower than 0.0000001 second of what SYSDATETIME() can return.

There are 3 main factors affecting the actual precision of the SYSDATETIME() function:

  • The precision of the time function of the server operating system.
  • The multi-tasking behavior of the server operating system, since the SQL Server task needs to be put on sleep frequently by the operating system.
  • The multi-session behavior of the SQL Server, since your client session task needs to be put on sleep frequently by the SQL server task.


Adding and Removing Days on Date and Time Values in SQL Server

Get System Date and Time in SQL Server Transact-SQL

Date/Time Operations and Functions in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials

2017-02-22, 1531🔥, 0💬