Don’t let date-related queries miss important records

You may think that querying the records between two dates is a simple process. But if you forget to consider both the date and time values, you may not get the results you want. And because such data omissions usually don’t cause software errors, they may never get fixed, and nobody will ever know what caused, say, someone’s medical records or banking transactions to go missing.

For instance, consider the following query:

SELECT *
FROM dbo.tblMeetings
WHERE MeetingTime
BETWEEN '10/11/2006' AND '10/12/2006'

Although at first glance this query appears as if it will return records for 10/11 and 10/12, it actually only returns records for 10/11.

SQL Server defaults to 0:00:00 AM because the query doesn’t specify a time. In other words, the BETWEEN clause evaluates to this:

BETWEEN '10/11/2006 00:00 AM' AND '10/12/2006 00:00 AM'

As a result, the query returns only those meetings that fall on October 11 because 00:00:00 AM is the beginning of October 12, not the end. By not specifying a time, your query eliminates an entire day from the results.

You can avoid the above error by explicitly entering a time value. For instance, the following query includes all records for the two days:

SELECT *
FROM dbo.tblMeetings
WHERE MeetingTime
BETWEEN '10/11/2006 00:00 AM'
AND '10/12/2006 11:59:59.998 PM'

A few important things to notice:

• BETWEEN is inclusive, so the query returns records that exactly match 10/11/2006 00:00 AM and 10/12/2006 11:59:59.998 PM (as well as everything in between).
• Just specifying 11:59 PM may not be good enough! You’ll miss records marked 11:59:01, for example. The data loss may so intermittent that the debugging team take days to locate the source of the problem.
• 11:59:59.999 PM evaluates to 12:00 AM the next day, so 11:59:59.998 PM is the highest possible time value for a day.

To avoid messing with hours, minutes, seconds, and fractions of seconds, you can consider the following somewhat cleaner alternative:

WHERE MeetingTime
>= '10/11/2006 00:00 AM'
AND MeetingTime
<'10/13/2006 00:00 AM'