在 SQL 中按日期过滤
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13869640/
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
Filter by Dates in SQL
提问by 007
I have a column in my table for dates (DateTime) and I am trying to create a WHERE clause that says, WHERE dates BETWEEN 12-11-2012 and 12-13-2012
我的表中有一个日期列（DateTime），我正在尝试创建一个 WHERE 子句，上面写着， WHERE dates BETWEEN 12-11-2012 and 12-13-2012
A sample value of dates column = 2012-05-24 00:38:40.260
日期列的示例值 = 2012-05-24 00:38:40.260
I want to say WHERE dates BETWEEN MM-DD-YYYY and MM-DD-YYYY.
我想说 WHERE 日期介于 MM-DD-YYYY 和 MM-DD-YYYY 之间。
I tried doing
我试着做
WHERE dates BETWEEN ((convert(nvarchar(10), dates,110) = '2012-12-12') AND (convert(nvarchar(10), dates,110) = '2012-12-12'))
but doesn't seem to work. "Incorrect syntax near ="
但似乎不起作用。“= 附近的语法不正确
Please help
请帮忙
EDIT:
编辑：
Thanks for various options and description guys. Got it working with @RichardTheKiwi's options.
感谢您提供各种选项和描述。让它与@RichardTheKiwi 的选项一起工作。
回答by RichardTheKiwi
If your datescolumn does not contain time information, you could get away with:
如果您的dates列不包含时间信息，您可以使用：
WHERE dates BETWEEN '20121211' and '20121213'
However, given your datescolumn is actually datetime, you want this
但是，鉴于您的dates列实际上是日期时间，您想要这个
WHERE dates >= '20121211'
AND dates < '20121214' -- i.e. 00:00 of the next day
Another option for SQL Server 2008 onwards that retains SARGability(ability to use index for good performance) is:
保留SARGability（使用索引以获得良好性能的能力）的SQL Server 2008 以后的另一个选项是：
WHERE CAST(dates as date) BETWEEN '20121211' and '20121213'
Note: alwaysuse ISO-8601 format YYYYMMDD with SQL Server for unambiguous date literals.
注意：始终将 ISO-8601 格式 YYYYMMDD 与 SQL Server 一起用于明确的日期文字。
回答by Hamlet Hakobyan
WHERE dates BETWEEN (convert(datetime, '2012-12-12',110) AND (convert(datetime, '2012-12-12',110))
回答by Kaf
Well you are trying to compare Date with Nvarchar which is wrong. Should be
好吧，您正在尝试将 Date 与 Nvarchar 进行比较，这是错误的。应该
Where dates between date1 And date2
-- both date1 & date2 should be date/datetime
If date1,date2 strings; server will convert them to date type before filtering.
如果 date1,date2 字符串；服务器将在过滤之前将它们转换为日期类型。
