where 子句中的 SQL DateDifference
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5266643/
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 DateDifference in a where clause
提问by DarthVader
I m doing a query as follows:
我正在做一个查询如下：
SELECT
*
FROM a
WHERE DATEDIFF(D, a.DateValue, DateTimeNow) < 3;
and not working
并且不工作
I m trying to get the data that s not older than 3 days.
我正在尝试获取不超过 3 天的数据。
SQL server.
SQL 服务器。
How to do this?
这该怎么做？
DATEDIFF works too slow..
DATEDIFF 工作太慢了..
回答by Charles Bretana
DateDiffis extremely fast... Your problem is you are running it on the database table column value, so the query processor must run the function on every row in the table, even if there was an index on this column. This means it has to load the entire table from disk.
DateDiff非常快...您的问题是您在数据库表列值上运行它，因此查询处理器必须在表中的每一行上运行该函数，即使该列上有索引。这意味着它必须从磁盘加载整个表。
Instead, use the dateAddfunction on todays date, and compare the database table column to the result of that single calculation. Now it only runs DateAdd()once, and it can use an index (if one exists), to only load the rows that match the predicate criterion.
相反，dateAdd在今天的日期使用该函数，并将数据库表列与该单个计算的结果进行比较。现在它只运行DateAdd()一次，并且可以使用索引（如果存在）来仅加载与谓词条件匹配的行。
Where a.DateValue > DateAdd(day,-3,getdate())
Where a.DateValue > DateAdd(day,-3,getdate())
doing this in this way makes your query predicate SARG-able
以这种方式执行此操作使您的查询谓词具有SARG 能力
回答by Gareth McCaughan
Microsoft's documentation at http://msdn.microsoft.com/en-us/library/aa258269%28v=sql.80%29.aspxsuggests that instead of DateTimeNowyou should have getdate(). Does it work any better that way?
微软在http://msdn.microsoft.com/en-us/library/aa258269%28v=sql.80%29.aspx 上的文档建议，而不是DateTimeNow你应该有getdate(). 这样效果会更好吗？
回答by cairnz
Your query doesn't seem to bad. Another way to tackle it would be:
您的查询似乎不错。解决它的另一种方法是：
SELECT * FROM a WHERE a.DateValue > DATEADD(dd,-3,GETDATE())
