Millisecond resolution in DATETIME comparison

_{Thursday, August 4, 2011 6:10 PM | 1 vote}

Hello,

I have a SQL Server 2k (ancient, I know) database in which I store DATETIME values. If I run DATEPART(ms, Time) on the field, it properly returns milliseconds. However, if I do a comparison using an ISO 8601 formatted string including milliseconds, I get inconsistent and often inaccurate results. For instance

SELECT *

FROM Table

WHERE (Time < '2011-08-02T05:07:27.000')

Will return results where the Time is '2011-08-02 05:07:27' with DATEPART(ms, Time) being equal to 955.

1.) Do <, >, and BETWEEN correctly include milliseconds in their comparisons?

2.) If not, what is the best/most efficient way to accomplish a comparison that takes into account milliseconds?

3.) If so, any idea what I might be doing wrong?

Thank you very much for any help you can provide.

_{Thursday, August 4, 2011 7:06 PM ✅Answered | 1 vote}

Yes - comparisons involving datatime datatypes are correct and do evaluate to the complete precision of the datatype.  Why you are experiencing problems is difficult to tell from a distance.  The first step is to post the DDL of the table involved.  It is good to see that we are dealing with datetime (and not smalldatetime or varchar or something else).  Secondly, it would help to see an actual example of the data that is giving inconsistent or inaccurate results.  Lastly, you should be aware that a datetime value is accurate to 3 ms; you need to be aware of this in your comparisons so that you choose the appropriate boundaries.  Below is the good source of additional information.

http://www.karaszi.com/sqlserver/info_datetime.asp

_{Thursday, August 4, 2011 7:08 PM ✅Answered | 2 votes}

In SQL 2005 and earlier, datetime is not accurate to the millisecond.  It is accurate to 1/300th of a second but provides the ability to show that in decimal format rounding the milliseconds to either 0, 3 or 7 in the final digit.

For example:

declare @test table(a varchar(7), b as convert(datetime, a))
insert into @test
select '0:0.001' union all select '0:0.002' union all
select '0:0.003' union all select '0:0.004' union all
select '0:0.005' union all select '0:0.006' union all
select '0:0.007' union all select '0:0.008' union all
select '0:0.009' union all select '0:0.010'

select * from @test
/*  Output: 
a    b
 
0:0.001 1900-01-01 00:00:00.000
0:0.002 1900-01-01 00:00:00.003
0:0.003 1900-01-01 00:00:00.003
0:0.004 1900-01-01 00:00:00.003
0:0.005 1900-01-01 00:00:00.007
0:0.006 1900-01-01 00:00:00.007
0:0.007 1900-01-01 00:00:00.007
0:0.008 1900-01-01 00:00:00.007
0:0.009 1900-01-01 00:00:00.010
0:0.010 1900-01-01 00:00:00.010
*/

In addition to Tibor's blog, another useful reference can be found here.

_{Thursday, August 4, 2011 7:49 PM ✅Answered}

Thank you all for the replies. I was aware of the resolution of datetime not going down to the millisecond, but it is certainly good to have that reinforced. datetime2 is so much nicer <sigh>.

Thanks again.

_{Thursday, August 4, 2011 7:08 PM}

Thank you for the reply scott_morris. I think I figured out what the issue is. It's a lack of understanding on my part. The date that was appearing in the database was being rounded up. So I took 05:07:27 and 955 milliseconds to be 05:07:27.955. Whereas it was actually 05:07:26.955. So things were functioning as they should, I just didn't take into account that the datetime display was being rounded to the nearest second.

_{Thursday, August 4, 2011 7:47 PM | 1 vote}

There is one more that is critical that you need to be aware of because it seems to cause the date to change:

declare @test table(a varchar(23), b as convert(datetime, a))
insert into @test
select '8/4/2011 23:59:59.999'

select * from @test
/*  Output: 
a            b
 
8/4/2011 23:59:59.999  2011-08-05 00:00:00.000
*/

This can be a problem!  What to remember is that SQL Server only stores the milliseconds to the nearest 1/300th of a second.  The decimal ".999" is more precise than SQL Server can directly handle.  Therefore, the .999 gets rounded UP.  In this case since it is rounded up it also means that the DAY also gets rounded up.  This rounding can surprise you, so watch out.

_{Thursday, August 4, 2011 8:05 PM}

You need to provide specific input data; as far as I have ever been able to tell over the past twenty two years, this works correctly -- for example:

declare @test table(a varchar(23), [time] as convert(datetime, a))
insert into @test
select '8/4/2011 23:59:59.999' union all
select '20110802 05:07:27.955'

select * from @test
where [time] < '2011-08-02T05:07:27.000'
/*  Output: 
a            time
 

(0 row(s) affected)
*/

.

_{Thursday, August 4, 2011 8:41 PM | 1 vote}

Your query allows times to be returned with "955" as milliseconds, just not for the datetime '20110802 05:07:27'.

Do you have a script to reproduce the problem, because it works fine for me (on Microsoft SQL Server  2000 - 8.00.2040).

Gert-Jan

_{Thursday, August 4, 2011 8:49 PM}

Hey Guys,

Sorry for the lack of clarity. I did find out that it was user error. See the post I marked as the answer for an explanation on why the rounding of the display was leading me to the wrong conclusions.

Thanks again for the help and suggestions.