Strange datetime behavior in SQL Server
datetime, sql, sql-server-2008
Solution
You have a precision problem. .999 is rounded up to .000.
.997 is as close to the next day as you can get.
declare @data datetime
set @data = '2011-01-01T23:59:59.997'
select @data
Have a look here at the section about "Rounding of datetime Fractional Second Precision" http://msdn.microsoft.com/en-us/library/ms187819.aspx
If you are on SQL Server 2008 you can use datetime2(3) if you want a precision down to the millisecond.
Problem
``` declare @data datetime set @data = '2011-01-01 23:59:59:999' select @data ``` result is: ``` 2011-01-02 00:00:00.000 ``` Second example: ``` declare @data datetime set @data = '2011-01-01 23:59:59:999' select 1 where @data >= '2011-01-02 00:00:00:000' ``` result ``` 1 ``` My question is why and how to make it correct? edit problem is in sql server 2008