0 votes
1 view
in SQL by (22.4k points)

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.

1 Answer

0 votes
by (40.3k points)

For larger date ranges also, it is (inclusive) i.e. work with a single date too. You are trying to compare datetimes to dates. But, the second date is interpreted as midnight when the day starts.

Use the below code to fix it:

SELECT *

FROM Cases

WHERE cast(created_at as date) BETWEEN '2013-05-01' AND '2013-05-01'

Otherwise, you can do explicit binary comparisons like this:

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.

Related questions

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...