如何选择特定日期的行，忽略 SQL Server 中的时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4156011/
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
How to select rows for a specific date, ignoring time in SQL Server
提问by DenaliHardtail
Given a table with a datetime column, how do I query for rows where the date matches the value I specify but ignores the time portion?
给定一个带有日期时间列的表，如何查询日期与我指定的值匹配但忽略时间部分的行？
For example, select * from sales where salesDate = '11/11/2010'
例如， select * from sales where salesDate = '11/11/2010'
For this query we don't care about the time. Other queries require the time component so we can't store only the date component.
对于这个查询，我们不关心时间。其他查询需要时间组件，所以我们不能只存储日期组件。
Thanks!
谢谢！
回答by Oded
You can remove the time component when comparing:
您可以在比较时删除时间组件：
SELECT *
FROM sales
WHERE CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, salesDate))) = '11/11/2010'
Another approach is to change the select to cover all the time between the start and end of the date:
另一种方法是更改选择以涵盖日期开始和结束之间的所有时间：
SELECT *
FROM sales
WHERE salesDate BETWEEN '11/11/2010 00:00:00.00' AND '11/11/2010 23:59:59.999'
回答by user111995
I know it's been a while on this question, but I was just looking for the same answer and found this seems to be the simplest solution:
我知道这个问题已经有一段时间了，但我只是在寻找相同的答案，发现这似乎是最简单的解决方案：
select * from sales where datediff(dd, salesDate, '20101111') = 0
I actually use it more to find things within the last day or two, so my version looks like this:
我实际上更多地使用它来查找最后一两天内的东西，所以我的版本看起来像这样：
select * from sales where datediff(dd, salesDate, getdate()) = 0
And by changing the 0 for today to a 1 I get yesterday's transactions, 2 is the day before that, and so on. And if you want everything for the last week, just change the equals to a less-than-or-equal-to:
通过将今天的 0 更改为 1，我可以获得昨天的交易，2 是前一天，依此类推。如果您想要上周的所有内容，只需将等于更改为小于或等于：
select * from sales where datediff(dd, salesDate, getdate()) <= 7
回答by AlexanderMP
select * from sales where salesDate between '11/11/2010' and '12/11/2010' --if using dd/mm/yyyy
The more correct way to do it:
更正确的做法是：
DECLARE @myDate datetime
SET @myDate = '11/11/2010'
select * from sales where salesDate>=@myDate and salesDate<dateadd(dd,1,@myDate)
If only the date is specified, it means total midnight. If you want to make sure intervals don't overlap, switch the between with a pair of >=and <
如果只指定了日期，则表示整个午夜。如果您想确保间隔不重叠，请使用一对>=和<
you can do it within one single statement, but it's just that the value is used twice.
您可以在一个语句中完成它，但只是该值被使用了两次。
回答by John Hartsock
Something like this:
像这样的东西：
select
*
from sales
where salesDate >= '11/11/2010'
AND salesDate < (Convert(datetime, '11/11/2010') + 1)
回答by Sean
select
*
from sales
where
dateadd(dd, datediff(dd, 0, salesDate), 0) = '11/11/2010'
回答by Sherif Hamdy
Try this:
尝试这个：
true
select cast(salesDate as date) [date] from sales where salesDate = '2010/11/11'
false
select cast(salesDate as date) [date] from sales where salesDate = '11/11/2010'
