SQL-Server Blog of Ritesh Shah --Fight the fear of SQL with SQLHub.com

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