DateTime observation in SQL Server 2008, Rounding or Bug?

Recently I come across with one very interesting observation. When you assign any date in datetime variable with milliseconds and print it, you will get difference of few milliseconds, sometime one or two or three. I don’t know whether SQL Server is rounding the datetime or it is a bug or SQL Server is not so much accurate up to milliseconds. Have a look at below examples:

declare @datetest datetime
set @datetest = ‘2009-01-01 23:59:59.998’
select @datetest –answer is 2009-01-01 23:59:59.997
GO

declare @datetest datetime
set @datetest = ‘2009-01-01 23:59:59.992’
select @datetest –answer is 2009-01-01 23:59:59.993
GO

declare @datetest datetime
set @datetest = ‘2009-01-01 23:59:59.982’
select @datetest –answer is 2009-01-01 23:59:59.983
GO

 –in this case you can see even day is changed because of one milliseconds 🙂

declare @datetest datetime
set @datetest = ‘2009-01-01 23:59:59.999’
select @datetest –answer is 2009-01-02 00:00:00.000
GO

I tried to find exact reason for this but wasn’t able to do so.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com

Advertisements

8 Responses to “DateTime observation in SQL Server 2008, Rounding or Bug?”

  1. Pinal Dave Says:

    Hi Ritesh,This is expected behavior of SQL Server DATETIME datatype. Same kind of accuracy is available in SQL Server 2000 and SQL Server 2005.SQL Server 2008 Datatype DATETIME2 which is much larger accuracy.Run above example again as following change.DECLARE @datetest DATETIME2SET @datetest = '2009-01-01 23:59:59.998'SELECT @datetest –answer is 2009-01-01 23:59:59.9980000GOKind Regards,Pinal

  2. Pinal Dave Says:

    Hi Ritesh,This is expected behavior of SQL Server DATETIME datatype. Same kind of accuracy is available in SQL Server 2000 and SQL Server 2005.SQL Server 2008 Datatype DATETIME2 which is much larger accuracy.Run above example again as following change.DECLARE @datetest DATETIME2SET @datetest = '2009-01-01 23:59:59.998'SELECT @datetest –answer is 2009-01-01 23:59:59.9980000GOKind Regards,Pinal

  3. Pinal Dave Says:

    One more thing,SQL Server DATETIMEhas precision of 3 millisecond. Just like SMALLDATETIME has precision of 1 min.Kind Regards,Pinal

  4. Pinal Dave Says:

    One more thing,SQL Server DATETIMEhas precision of 3 millisecond. Just like SMALLDATETIME has precision of 1 min.Kind Regards,Pinal

  5. Ritesh Shah Says:

    Hi Pinal,Thank you very much for your response. I really appreciate it. BTW, does it mean that DateTime data type is not accurate up to milliseconds?

  6. Ritesh Shah Says:

    Hi Pinal,Thank you very much for your response. I really appreciate it. BTW, does it mean that DateTime data type is not accurate up to milliseconds?

  7. Pinal Dave Says:

    Yes, DATETIME has precision of only 3 milisecond.

  8. Pinal Dave Says:

    Yes, DATETIME has precision of only 3 milisecond.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: