SQL “between” not inclusive
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.
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.