DateTime comparisons in SQL Server 2008 and 2012

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)

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)

MessageBox.Show(this, DateTime.Parse("
             9/1/08 00:00:00.001").ToString("HH:mm:ss.ffff"));

Output: 00:00:00.0010

Comments (6)

1. 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

    

2. 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.

3. 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. 

4. 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.

5. 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.

6. 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.