Strange datetime behavior in SQL Server

Question

 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

My question is why and how to make it correct?

edit

problem is in sql server 2008

what do you wanne do? make a check if the date is bigger than '2011-01-02 00:00:00:000' and return 1 if so? — Saeed, Aug 23 '11 at 08:08

Mikael Eriksson · Accepted Answer · 2013-04-11T08:16:44.410

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.

score 2 · Answer 2 · answered Aug 23 '11 at 08:11

2

You need to use more precise format - datetime2 (YYYY-MM-DD HH:MM:SS:XXXXXXX)

 declare @data datetime2
 set @data = '2011-01-01 23:59:59:999'
 select 1 where @data >= '2011-01-02 00:00:00:000'

answered Aug 23 '11 at 08:11

Silx