7

I came across something that I think has to do with time resolution in SQL. Here's an example I've used:

CREATE TABLE #table
(
    DTstamp DATETIME NOT NULL
)

INSERT INTO #table VALUES ('1 apr 2016 15:01:02:129')

SELECT DTstamp FROM #table

DROP TABLE #table

The result from SELECT shown in SQL Server Management Studio is off by 0.001 in this case:

2016-04-01 15:01:02.130

When trying multiple values, the result is shown is usually within 0.003 sec of the inserted time. Does anyone know where the discrepancy come from?

SQL Version:

Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) 
Oct 20 2015 15:36:27 
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Andrew
  • 73
  • 1
  • 1
  • 3
  • Possible duplicate of [Why is SQL Server losing a millisecond?](https://stackoverflow.com/questions/715432/why-is-sql-server-losing-a-millisecond) – Pᴇʜ May 03 '19 at 10:08

1 Answers1

14

According to MSDN https://msdn.microsoft.com/en-CA/library/ms187819.aspx, the accuracy of [DateTime] data type is "Rounded to increments of .000, .003, or .007 seconds"

To solve your issue, use DateTime2(3) as shown below:

CREATE TABLE #table
(
    DTstamp DATETIME2(3) NOT NULL
)
INSERT INTO #table VALUES ('1 apr 2016 15:01:02:129')
SELECT DTstamp FROM #table
DROP TABLE #table
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
jyao
  • 1,550
  • 3
  • 19
  • 26