O'Reilly logo
  • Dustin Waybright thinks this is interesting:

WHERE orderdate BETWEEN '20070201' AND '20070228 23:59:59.999'

This type’s precision is three and a third milliseconds. The milliseconds part of the end point 999 is not a multiplication of the precision unit, so SQL Server ends up rounding the value to midnight of March 1, 2007. As a result, you may end up getting some orders that you’re not supposed to see. In short, instead of BETWEEN, use >= and <, and this form will work correctly in all cases, with all date and time types, whether the time portion is applicable or not.


Cover of Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012


The BETWEEN function can cause issues around time precision. Best to use >= and < to include whole dates.