Prevent date-related queries from missing 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. Nobody will ever know what caused, say, someone′s medical records or banking transactions to go missing.

For instance, consider the following query against a table using the timestamp data type with no fractional seconds—i.e., timestamp(0) (the same principles apply to date, or timestamp with fractional seconds, although the notation in our examples would differ slightly):
SELECT *
FROM meetings
WHERE meeting_time
BETWEEN '11-JUN-07' AND '12-JUN-07';

Although at first glance this query appears as if it will return records for 6/11 and 6/12, in practice it′s likely to return records only for 6/11.
Oracle defaults to 12.00.00 AM because the query doesn′t specify a time. In other words, the BETWEEN clause evaluates to this:
BETWEEN '11-JUN-07 12.00.00 AM' AND '12-JUN-07 12.00.00 AM'
  
As a result, the query returns only those meetings that fall on June 11 because 12.00.00 AM is the beginning of June 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 meetings
WHERE meeting_time
BETWEEN '11-JUN-07 12.00.00 AM'
AND '12-JUN-07 11.59.59 PM';

A few important things to notice:

• BETWEEN is inclusive, so the query returns records that exactly match 11-JUN-07 12.00.00 AM and 12-JUN-07 11.59.59 PM (as well as everything in between).
• Just specifying a time that evaluates to 11.59.00 PM may not be good enough. You′ll miss records marked 11.59.01, for example. The data loss may be so intermittent that the debugging team take days to locate the source of the problem.

To avoid messing with hours, minutes, seconds—and better accommodate fractions of seconds—consider the following even better alternative:
WHERE meeting_time
>= '11-JUN-07 12.00.00 AM'
AND meeting_time
< '13-JUN-07 12.00.00 AM'