“之间的SQL”不包容 [英] SQL "between" not inclusive

问题描述

我有一个这样的查询：

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

但是即使在第一个数据上也没有结果。 / p>

created_at 看起来像 2013-05-01 22:25:19 ，我怀疑这跟时间有关系？如何解决？

如果我做更大的日期范围，它的工作正常，但它应该（包括）也可以使用一个日期。

解决方案

它是。您正在将数据时间与日期进行比较。

解决这个问题的一种方法是：

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

另一种解决方法是使用显式的二进制比较

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

Aaron Bertrand在日期上有一个长的博客条目（这里），在那里他讨论这个和其他日期问题。

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.

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