SQL“之间"不包括在内 [英] SQL "between" not inclusive

问题描述

我有一个这样的查询:

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

但是即使 1 号有数据，这也没有结果.

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

created_at 看起来像 2013-05-01 22:25:19，我怀疑这与时间有关?怎么解决?

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 有一篇关于日期的长博客条目 (此处)，他在此处讨论了这个和其他日期问题.

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

这篇关于SQL“之间"不包括在内的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！