Why is SQL Server losing a millisecond?

Question

I have a table structured like this:

CREATE TABLE [TESTTABLE]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DateField] [datetime] NULL,
    [StringField] [varchar](50),
    [IntField] [int] NULL,
    [BitField] [bit] NULL
)

I execute the following code:

BEGIN 
   INSERT INTO TESTTABLE (IntField, BitField, StringField, DateField) 
   VALUES ('1', 1, 'hello', {ts '2009-04-03 15:41:27.378'});  

   SELECT SCOPE_IDENTITY()  
END

And then

select * from testtable with (NOLOCK)

and my result shows:

2009-04-03 15:41:27.*377*

for the DateField column.

Any ideas why I seem to be losing a millisecond??

i noted same mistake with dates like '2008-12-31 23:59:59.999' — Jhonny D. Cano -Leftware-, Apr 03 '09 at 19:53
look at this: SELECT CONVERT(DATETIME, '2008-12-31 23:59:59.999') — Jhonny D. Cano -Leftware-, Apr 03 '09 at 19:53
[See this question](http://stackoverflow.com/questions/634122) for more details — John M Gant, Apr 03 '09 at 19:55

Whatsit · Accepted Answer · 2009-04-03T20:27:08.300

100

SQL Server only stores time to approximately 1/300th of a second. These always fall on the 0, 3 and 7 milliseconds. E.g. counting up from 0 in the smallest increment:

00:00:00.000
00:00:00.003
00:00:00.007
00:00:00.010
00:00:00.013
...

If you need that millisecond accuracy, there's no pleasant way around it. The best options I've seen are to store the value in custom number fields and rebuild it every time you fetch the value, or to store it as a string of a known format. You can then (optionally) store an 'approximate' date in the native date type for the sake of speed, but it introduces a conceptual complexity that often isn't wanted.

edited Apr 03 '09 at 20:27

answered Apr 03 '09 at 20:00

Whatsit

10,227
11
42
41

13

Be sure to read Rob Garrison's post about TIME and DATETIME2 -- these types have much higher resolution. – Dave Markle Aug 17 '12 at 18:28
According to my tests on Azure SQL as of today, all of my DateTime fields have milliseconds with ones digits of 0, 3, and 6 – Jason Dufair Apr 03 '20 at 18:38

score 45 · Answer 2 · edited Apr 22 '14 at 16:52

45

SQL Server 2008 has much more precision available. The datetime2 type will accurately store values like this: 2008-12-19 09:31:38.5670514 (accuracy to 100 nanoseconds).

Reference: time and datetime2 - Exploring SQL Server 2008's New Date/Time Data Types

edited Apr 22 '14 at 16:52

nikib3ro

20,366
24
120
181

answered Apr 03 '09 at 20:01

Rob Garrison

6,984
4
25
23

score 29 · Answer 3 · edited Oct 15 '17 at 06:55

29

The SQL Server datetime type only has a 1/300th of a second (~3.33̅ ms) resolution, so you are probably seeing a rounding error.

See the MSDN Datetime SQL Server reference

edited Oct 15 '17 at 06:55

Eli Rose

6,788
8
35
55

answered Apr 03 '09 at 19:51

Peter M

7,309
3
50
91

score 7 · Answer 4 · edited Sep 13 '13 at 11:51

7

SQL Server is only accurate to 1/300th of a second. It will round values to the nearest 1/300th.

edited Sep 13 '13 at 11:51

TheCloudlessSky

18,608
15
75
116

answered Apr 03 '09 at 19:51

Tom H

46,766
14
87
128

Thanks for the responses. Makes me feel better. I just have to adjust my unit tests. :) – sproketboy Apr 03 '09 at 19:57
1

Close, 3/1000 resolution would be 000, 003, 006, 009... 1/300 resolution is 000, 003, 007, 010... – Eclipse Apr 03 '09 at 20:03

score 3 · Answer 5 · answered Apr 03 '09 at 19:52

3

DATETIME does not have infinite precision - you are probably using a value that cannot accurately be represented with the available bits.

answered Apr 03 '09 at 19:52

score 2 · Answer 6 · edited Aug 22 '18 at 20:40

2

SQL Server stores datetime values to a precision of 3 milliseconds. (I've heard about this, but can't find an official reference.)

edited Aug 22 '18 at 20:40

Bhargav Rao

50,140
28
121
140

answered Apr 03 '09 at 19:52

bdukes

152,002
23
148
175

Why is SQL Server losing a millisecond?

6 Answers6

Linked

Related