SQL "between" not inclusive

Question

I have a query like this:

SELECT * FROM Cases WHERE created_at BETWEEN '2013-05-01' AND '2013-05-01'

But this gives no results even though there is data on the 1st.

created_at looks like 2013-05-01 22:25:19, I suspect it has to do with the time? How could this be resolved?

It works just fine if I do larger date ranges, but it should (inclusive) work with a single date too.

Answer 1

It is inclusive. You are comparing datetimes to dates. The second date is interpreted as midnight when the day starts.

One way to fix this is:

SELECT *
FROM Cases
WHERE cast(created_at as date) BETWEEN '2013-05-01' AND '2013-05-01'

Another way to fix it is with explicit binary comparisons

SELECT *
FROM Cases
WHERE created_at >= '2013-05-01' AND created_at < '2013-05-02'

Aaron Bertrand has a long blog entry on dates (here), where he discusses this and other date issues.

Answer 2

It has been assumed that the second date reference in the BETWEEN syntax is magically considered to be the "end of the day" but this is untrue.

i.e. this was expected:

SELECT * FROM Cases 
WHERE created_at BETWEEN the beginning of '2013-05-01' AND the end of '2013-05-01'

but what really happen is this:

SELECT * FROM Cases 
WHERE created_at BETWEEN '2013-05-01 00:00:00+00000' AND '2013-05-01 00:00:00+00000'

Which becomes the equivalent of:

SELECT * FROM Cases WHERE created_at = '2013-05-01 00:00:00+00000'

---

The problem is one of perceptions/expectations about BETWEEN which does include BOTH the lower value and the upper values in the range, but does not magically make a date the "beginning of" or "the end of".

BETWEEN should be avoided when filtering by date ranges.

Always use the >= AND < instead

SELECT * FROM Cases 
WHERE (created_at >= '20130501' AND created_at < '20130502')

the parentheses are optional here but can be important in more complex queries.