Error SQL between not inclusive

Question

I have a query like this:

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

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

created_at looks like 2020-05-01 13:15:9, 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

Hello,

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 '2020-05-01' AND '2013-05-08'

Another way to fix it is with explicit binary comparisons

SELECT *
FROM Cases
WHERE created_at >= '2020-05-01' AND created_at < '2020-05-08'

Hope this works!!