WHERE my_event_start BETWEEN '20100601' and '20100630 23:59:59.999'

?
Well frankly, no again. TSQL can do weird rounding things to milliseconds because it's granularity is coarser than the millisecond. I'm pretty sure .999 will be rounded to the next second which is 20100701 00.00.000. So what's the answer?

Don't use BETWEEN when dealing with datetimes. Use:

WHERE my_event_start >= '20100601' and my_event_start < '20100701'

Edit question: The above should say ">=" and "<". How do I make the IFcode shortcuts cooperate?

PRINT CONVERT(VARCHAR, CAST('01/01/2011 23:59:59.998' AS DATETIME), 121)
PRINT CONVERT(VARCHAR, CAST('01/01/2011 23:59:59.999' AS DATETIME), 121)

WHERE my_event_start BETWEEN '20100601' AND '20100630 23:59:59.997'

jbnv (6/26/2011)

---

Good article, but is the political correctness really necessary? The use of the term "Common Era" to describe the Christian-based calendar is offensive. So is the comment about not using "Ed the Gregorian"'s calendar (who?) anymore. The tone that "we" don't use the Christian calendar "any more" is especially offensive to Christians and other groups who use custom calendars for whatever reason.

Eric M Russell (6/17/2011)

---

The BETWEEN operator is problematic when used with datetime, floating point numbers, and other non-discrete data types. When selecting rows where a date falls within a range, I'll use the following method:

where enrollment_date >= '2011-06-01' and enrollment_date < '2011-07-01';