Answer

Using BETWEEN with DATETIMEs in SQL

Expert Rudy Limeback is asked if SQL can be used to retrieve data between two dates, including the two dates.

Rudy Limeback
By
Published: 03 Jul 2008

Can we retrieve data between two dates, including the two dates? For example, if I require data from 01/01/2008 to 31/03/2008, using "between" only gives the data from 02/01/2008 to 30/03/2008. Is there any way we can get the data including the dates 01/01/2008 and also 31/03/2008?

You may not have given enough information for me to diagnose your problem correctly. BETWEEN does include the endpoints of the range. This is the default behaviour.

What might be happening is that you actually have datetime values in your table, rather than date values. For example, let's say the values in your table are:

myDateTime
2008-01-01 01:01
2008-01-22 01:22
2008-02-01 02:01
2008-02-29 02:29
2008-03-30 03:30
2008-03-31 09:37

Now run a query with this condition:

WHERE myDateTime BETWEEN '2008-01-01' 
                     AND '2008-03-31'

You will see that the last value, 2008-03-31 09:37, is missing. This is because it is outside the range specified by the BETWEEN condition. The reason is due to fact that the values in the BETWEEN condition are converted to datetime values in order to do the comparisons, so the condition is actually:

WHERE myDateTime BETWEEN '2008-01-01 00:00:00' 
                     AND '2008-03-31 00:00:00'

Thus, all the datetime values for that last day will be missing (except for those, if any, which specify midnight exactly).

When dealing with datetimes like this, you could try to specify the upper end of the range explicitly, like this:

WHERE myDateTime BETWEEN '2008-01-01' 
                     AND '2008-03-31 23:59:59.993'

However, the problem with this is that you have to be sure you specify the actual last possible datetime value for that date, and this becomes quite tricky as it depends on which version of which DBMS you're using.

A much better approach is to abandon the BETWEEN condition, and do this instead:

WHERE myDateTime >= '2008-01-01' 
  AND myDateTime  < '2008-04-01'

Notice that the operator in the second condition is less than, not less than or equal.

The bonus here is that you don't have to waste any time calculating the last day of the month (28th, 29th, 30th, or 31st). Just make it less than the 1st of the following month. Easier to code, just as efficient, and guaranteed to get all included datetime values no matter what precision the database uses (microsecond, 3 microseconds, whatever).

Neat, eh?

Dig Deeper on Oracle development languages