DateTime comparisons in SQL Server 2007
Aug 28th, 2008 | By Christian Donner | Category: SQL ServerThe issue is that SQL Server’s DateTime data type appears to be able to resolve milliseconds – but it really does not. It rounds to the nearest 0, 3, or 7.
Consider the following:
select case when convert(datetime, '9/1/08 00:00:00.005') between convert(datetime, '9/1/08 00:00:00.006') and convert(datetime, '9/1/08 00:00:00.007') then 'true' else 'false' end ---- true (1 row(s) affected)
This query returns ‘true’ because all three datetime values are rounded to 007 milliseconds when converted:
select convert(varchar(32), convert(datetime,
'9/1/08 00:00:00.005'), 121);
---
2008-09-01 00:00:00.007
(1 row(s) affected)
In C# and .Net on the other hand, the DateTime type resolves milliseconds correctly:
MessageBox.Show(this, DateTime.Parse("
9/1/08 00:00:00.001").ToString("HH:mm:ss.ffff"));
Output: 00:00:00.0010
This can have very undesirable effects when you pass parameters from .Net into a SQL query. Your DateTime value gets rounded in the database. Range comparisons can become unpredictable. If your query compares the passed-in date parameter to other dates in the database, if may fall outside a range, even though in reality it is in the range.
Similarily, a set of different DateTime values in .Net could become the same values in SQL, creating duplicates where you don’t expect them.
The reason for this is that SQL Server’s internal resolution for DateTime is 3.33 ms (as explained by Steve Kozak in SQL Server Professional) – which is larger than the least significant digit.
You’re not working with dates. ’01/01/08′ isn’t a date. Not to any computer functioning. It’s a string.Take your second query and replace it with real dates and it works fine.DECLARE @Jan108 datetimeDECLARE @Feb108 datetimeDECLARE @Dec3107 datetime
SET @Jan108 = ’1/1/08′SET @Feb108 = ’2/1/08′SET @Dec3107 = ’12/31/07′
select case when @Jan108 between @Jan108 and @Feb108 then ‘in’ else ‘out’ end as s1,
case when @Jan108 between @Dec3107 and @Jan108 then ‘in’ else ‘out’ end as s2
Mark,
I did not approve your other comment. You cannot possibly have read the post in the time since I commented on Stackoverflow.com.
I used the strings for illustration purposes, which I realize may not have been the smartest thing. It does not change the underlying issue.
Please read up on the topic before you insult people.
I’m unsure what you mean. I couldn’t possible have read … You’re basing your editorial decisions on reading speed? That’s a cop-out.
No, I am not basing editorial decisions on reading speed. I am basing my openess to criticism on the probability that you actually read the post.
That said, and after looking at it myself, I have to admit that I must have been delirious when I wrote it. I appreciate that you pointed this out to me. I do not appreciate the way you did it, though.
Either way, I will rewrite it when I am back from vacation, or whenever I get to it.
It’s disingenuous to say you wrote this deliriously. You quoted yourself 6 months later on Stack Overflow. This wasn’t something I pointed out the following day and you fixed. You wrote this in August and referenced yourself in February. You’ve believed this was true for 6 months.
It’s also disingenuous to say you appreciate me pointing it out. When I added a comment to your answer on SO (that seems to be deleted) you defended yourself and told me that I should “know what I’m talking about before accusing you of being wrong”. Even when someone tried to point out the error you were defending it, and you weren’t open to correction.
All I’m asking is before you post things, in multiple locations, that you understand what it is you’re posting. We need less disinformation not more.
Yes, I made a mistake 8 months ago, and nobody else seems to have read the posting since then, because nobody pointed out the mistake. I did not reread it when I posted the link on SO. That’s why peer-reviewed sites like SO are great. Again, I appreciate the opportunity to correct myself. Now please get over it.