The resolution of SYSTEMDATE function

  • Article

In SQL Server 2008, the datetime2, time, and datetimeoffset types can have a optional fractional second part. This decision have several benefit:

  1. If user does not care about the fractional second, they can use datetime2/time/datetimeoffset(0), which can save storage size, also and have a better display result (converting to string will not have factional second as well).
  2. If user have high resolution data, they can use datetime2/time/datetimeoffset(7)  (they have a precision of 100ns) to preserve the data. The 100ns precision is aligned with Windows FileTime and .Net DateTime class which provide better cross product support.

However, the T-SQL function SYSTEMDATETIME() and SYSTEDATETIMEOFFST() function both can not return values with resultion unit of 100ns.  Both functions call Windows Timer functions. The resolution of these functions depends on dependent on your timer tick rate, typically 10ms to 55ms.  It means that the last 5 digit of SYSTEMDATETIME()'s return value is not accurate.  It is possible that user can get high-resolution times from QueryPerformanceCounter function. Hopely, SQL Server will call these functions directly, so that we can enjoy the 100ns time resultion.