SQL 在sql查询的where子句中检查当前日期的最佳方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/325871/
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
Best way to check for current date in where clause of sql query
提问by Dale Marshall
I'm trying to find out the most efficient (best performance) way to check date field for current date. Currently we are using:
我试图找出检查当前日期的日期字段的最有效（最佳性能）方法。目前我们正在使用：
SELECT COUNT(Job) AS Jobs
FROM dbo.Job
WHERE (Received BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)
AND DATEADD(d, DATEDIFF(d, 0, GETDATE()), 1))
回答by Tomalak
WHERE
DateDiff(d, Received, GETDATE()) = 0
Edit: As lined out in the comments to this answer, that's not an ideal solution. Check the other answers in this thread, too.
编辑：正如对此答案的评论中所述，这不是理想的解决方案。也请检查此线程中的其他答案。
回答by Charles Bretana
If you just want to find all the records where the Received Date is today, and there are records with future Received dates, then what you're doing is (very very slightly) wrong... Because the Between operator allows values that are equal to the ending boundary, so you could get records with Received date = to midnight tomorrow...
如果您只想查找接收日期为今天的所有记录，并且有未来接收日期的记录，那么您所做的（非常非常轻微）是错误的......因为Between运算符允许值相等到结束边界，因此您可以获取 Received date = 到明天午夜的记录...
If there is no need to use an index on Received, then all you need to do is check that the date diff with the current datetime is 0...
如果不需要在 Received 上使用索引，那么您需要做的就是检查当前日期时间的日期差异是否为 0...
Where DateDiff(day, received, getdate()) = 0
This predicate is of course not SARGable so it cannot use an index... If this is an issue for this query then, assuming you cannot have Received dates in the future, I would use this instead...
这个谓词当然不是 SARGable 所以它不能使用索引......如果这是这个查询的问题，那么假设你将来不能有 Received 日期，我会用它来代替......
Where Received >= DateAdd(day, DateDiff(Day, 0, getDate()), 0)
If Received dates can be in the future, then you are probably as close to the most efficient as you can be... (Except change the Between to a >= AND < )
如果 Received 日期可以在未来，那么您可能会尽可能地接近最高效的...（除了将 between 更改为 >= AND < ）
回答by Marc Gravell
If you want performance, you want a directhit on the index, without any CPU etc per row; as such, I would calculate the range first, and then use a simple WHERE query. I don't know what db you are using, but in SQL Server, the following works:
如果你想要性能，你想要直接命中索引，每行没有任何 CPU 等；因此，我将计算范围第一，然后用一个简单的WHERE查询。我不知道您使用的是什么数据库，但在 SQL Server 中，以下操作有效：
// ... where @When is the date-and-time we have (perhaps from GETDATE())
DECLARE @DayStart datetime, @DayEnd datetime
SET @DayStart = CAST(FLOOR(CAST(@When as float)) as datetime) -- get day only
SET @DayEnd = DATEADD(d, 1, @DayStart)
SELECT COUNT(Job) AS Jobs
FROM dbo.Job
WHERE (Received >= @DayStart AND Received < @DayEnd)
回答by saurabh_hcl
Compare two dates after converting into same format like below.
转换为相同格式后比较两个日期，如下所示。
where CONVERT(varchar, createddate, 1) = CONVERT(varchar, getdate(), 1);
回答by Mladen Prajdic
that's pretty much the best way to do it. you could put the DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) and DATEADD(d, DATEDIFF(d, 0, GETDATE()), 1) into variables and use those instead but i don't think that this will improve performance.
这几乎是最好的方法。您可以将 DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) 和 DATEADD(d, DATEDIFF(d, 0, GETDATE()), 1) 放入变量并使用它们，但我不认为这将提高性能。
回答by ???u
I'm not sure how you're defining "best" but that will work fine.
我不确定您如何定义“最佳”，但这会很好用。
However, if this query is something you're going to run repeatedly you should get rid of the get_date() function and just stick a literal date value in there via whatever programming language you're running this in. Despite their output changing only once every 24 hours, get_date(), current_date(), etc. are non-deterministicfunctions, which means that your RDMS will probably invalidate the query as a candidate for storing in its query cache if it has one.
但是，如果这个查询是你要重复运行的东西，你应该去掉 get_date() 函数，并通过你运行它的任何编程语言在那里粘贴一个文字日期值。 尽管它们的输出只改变一次每 24 小时，get_date()、current_date() 等都是非确定性函数，这意味着您的 RDMS 可能会使查询无效，作为存储在其查询缓存中的候选对象（如果有）。
回答by James Curran
How 'bout
怎么样
WHERE
DATEDIFF(d, Received, GETDATE()) = 0
回答by kristof
I would normally use the solution suggested by Tomalak, but if you are really desperate for performance the best option could be to add an extra indexed field ReceivedDataPartOnly - which would store data without the time part and then use the query
我通常会使用Tomalak 建议的解决方案，但如果您真的非常渴望性能，最好的选择可能是添加一个额外的索引字段 ReceivedDataPartOnly - 它将存储没有时间部分的数据，然后使用查询
declare @today as datetime
set @today = datediff(d, 0, getdate())
select
count(job) as jobs
from
dbo.job
where
received_DatePartOnly = @today
