SQL“之间”不包括在内
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16347649/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL "between" not inclusive
提问by JBurace
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_atlooks like 2013-05-01 22:25:19, I suspect it has to do with the time? How could this be resolved?
created_at看起来像2013-05-01 22:25:19，我怀疑这与时间有关？这怎么解决？
It works just fine if I do larger date ranges, but it should (inclusive) work with a single date too.
如果我做更大的日期范围，它工作得很好，但它也应该（包括）使用单个日期。
回答by Gordon Linoff
It isinclusive. 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.
Aaron Bertrand 有一篇关于日期的长博客条目（这里），他在其中讨论了这个和其他日期问题。
回答by Used_By_Already
It has been assumed that the second date reference in the BETWEENsyntax is magically considered to be the "end of the day" but this is untrue.
已经假设BETWEEN语法中的第二个日期引用神奇地被认为是“一天的结束”，但这是不正确的。
i.e. this was expected:
即这是预期的：
SELECT * FROM Cases WHERE created_at BETWEEN the beginning of '2013-05-01' AND the end of '2013-05-01'
but what really happen is this:
但真正发生的是：
SELECT * FROM Cases WHERE created_at BETWEEN '2013-05-01 00:00:00+00000' AND '2013-05-01 00:00:00+00000'
Which becomes the equivalent of:
这相当于：
SELECT * FROM Cases WHERE created_at = '2013-05-01 00:00:00+00000'
The problem is one of perceptions/expectations about BETWEENwhich doesinclude BOTH the lower value and the upper values in the range, but does notmagically make a date the "beginning of" or "the end of".
问题是一种看法/期望，BETWEEN其中确实包括范围内的下限值和上限值，但不会神奇地使日期成为“开始”或“结束”。
BETWEENshould be avoided when filtering by date ranges.
BETWEEN按日期范围过滤时应避免使用。
Alwaysuse the >= AND <instead
总是使用>= AND <代替
SELECT * FROM Cases WHERE (created_at >= '20130501' AND created_at < '20130502')
the parentheses are optional here but can be important in more complex queries.
括号在这里是可选的，但在更复杂的查询中可能很重要。
回答by Barranka
You need to do one of these two options:
您需要执行以下两个选项之一：
- Include the time component in your
betweencondition:... where created_at between '2013-05-01 00:00:00' and '2013-05-01 23:59:59'(not recommended... see the last paragraph) - Use inequalities instead of
between. Notice that then you'll have to add one day to the second value:... where (created_at >= '2013-05-01' and created_at < '2013-05-02')
- 在您的
between条件中包含时间部分：（... where created_at between '2013-05-01 00:00:00' and '2013-05-01 23:59:59'不推荐...见最后一段） - 使用不等式代替
between。请注意，您必须将一天添加到第二个值：... where (created_at >= '2013-05-01' and created_at < '2013-05-02')
My personal preference is the second option. Also, Aaron Bertrandhas a very clear explanation on whyit should be used.
我个人的偏好是第二种选择。此外，阿龙贝特朗有为什么一个非常明确的解释应该使用它。
回答by Balinti
Just use the time stamp as date:
只需使用时间戳作为日期：
SELECT * FROM Cases WHERE date(created_at)='2013-05-01'
回答by Ted
I find that the best solution to comparing a datetime field to a date field is the following:
我发现将日期时间字段与日期字段进行比较的最佳解决方案如下：
DECLARE @StartDate DATE = '5/1/2013',
@EndDate DATE = '5/1/2013'
SELECT *
FROM cases
WHERE Datediff(day, created_at, @StartDate) <= 0
AND Datediff(day, created_at, @EndDate) >= 0
This is equivalent to an inclusive between statement as it includes both the start and end date as well as those that fall between.
这等效于包含在语句之间，因为它包括开始日期和结束日期以及介于两者之间的日期。
回答by Ali Adravi
cast(created_at as date)
That will work only in 2008 and newer versions of SQL Server
这只适用于 2008 和更新版本的 SQL Server
If you are using older version then use
如果您使用的是旧版本，请使用
convert(varchar, created_at, 101)
回答by abhishek kumar
You can use the date()function which will extract the date from a datetime and give you the result as inclusive date:
您可以使用date()从日期时间中提取日期并将结果作为包含日期的函数：
SELECT * FROM Cases WHERE date(created_at)='2013-05-01' AND '2013-05-01'
回答by Muralidharan C
Dyamic date BETWEEN sql query
动态日期 BETWEEN sql 查询
var startDate = '2019-08-22';
var Enddate = '2019-10-22'
let sql = "SELECT * FROM Cases WHERE created_at BETWEEN '?' AND '?'";
const users = await mysql.query( sql, [startDate, Enddate]);
