DateTime observation in SQL Server 2008, Rounding or Bug?

Wednesday, July 29, 2009 |

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

4 comments:

Pinal Dave said...

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 DATETIME2
SET @datetest = '2009-01-01 23:59:59.998'
SELECT @datetest --answer is 2009-01-01 23:59:59.9980000
GO

Kind Regards,
Pinal

Pinal Dave said...

One more thing,

SQL Server DATETIMEhas precision of 3 millisecond.

Just like SMALLDATETIME has precision of 1 min.

Kind Regards,
Pinal

Ritesh Shah said...

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?

Pinal Dave said...

Yes,

DATETIME has precision of only 3 milisecond.