在 SQL 中按日期过滤 [英] Filter by Dates in SQL

问题描述

我的表中有一个用于日期 (DateTime) 的列，我正在尝试创建一个 WHERE 子句，上面写着 WHERE 日期 BETWEEN 12-11-2012 和 12-13-2012

日期列的示例值 = 2012-05-24 00:38:40.260

我想说 WHERE 日期 BETWEEN MM-DD-YYYY 和 MM-DD-YYYY.

我尝试过

WHERE 日期 BETWEEN ((convert(nvarchar(10),dates,110) = '2012-12-12') AND (convert(nvarchar(10),dates,110) = '2012-12-12'))

但似乎不起作用."附近的语法不正确 ="

请帮忙

感谢您提供各种选项和描述.与@RichardTheKiwi 的选项配合使用.

解决方案

如果您的 dates 列不包含时间信息，您可以使用:

WHERE 日期介于 '20121211' 和 '20121213' 之间

但是，鉴于您的 dates 列实际上是日期时间，您想要这个

WHERE 日期 >='20121211'AND 日期 <'20121214' -- 即第二天的 00:00

SQL Server 2008 之后的另一种选择，它保留了 SARGability(使用索引以获得良好性能的能力)) 是:

WHERE CAST(dates as date) BETWEEN '20121211' 和 '20121213'

注意:总是将 ISO-8601 格式 YYYYMMDD 与 SQL Server 一起用于明确的日期文字.

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

A sample value of dates column = 2012-05-24 00:38:40.260

I want to say WHERE dates BETWEEN MM-DD-YYYY and 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.

解决方案

If your dates column does not contain time information, you could get away with:

WHERE dates BETWEEN '20121211' and '20121213'

However, given your dates column is actually datetime, you want this

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:

WHERE CAST(dates as date) BETWEEN '20121211' and '20121213'

Note: always use ISO-8601 format YYYYMMDD with SQL Server for unambiguous date literals.

这篇关于在 SQL 中按日期过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！