CODEWITHSUNDEEP
sqlsql-server
sproketboy
sproketboy

Reputation: 9452

Why is SQL Server losing a millisecond?

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??

Upvotes: 74

Views: 35926

Answers (6)

bdukes
bdukes

Reputation: 155985

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

Upvotes: 2

Peter M
Peter M

Reputation: 7493

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

Upvotes: 29

Rob Garrison
Rob Garrison

Reputation: 7244

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

Upvotes: 45

Tom H
Tom H

Reputation: 47382

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

Upvotes: 7

Whatsit
Whatsit

Reputation: 10635

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.

Upvotes: 101

anon
anon

Reputation:

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

Upvotes: 3

Related Questions