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.
- 3. Filtering and Sorting Data
- from Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012
- Publisher: Microsoft Press
- Released: December 2012
The BETWEEN function can cause issues around time precision. Best to use >= and < to include whole dates.
Share this highlighthttp://www.safaribooksonline.com/a/training-kit-exam/18779/