where子句中的SQL DateDifference

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工作太慢了. .

3 个解决方案

#1

DateDiff is 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 dateAdd function 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函数在todays date上，并将数据库表列与单个计算的结果进行比较。现在它只运行DateAdd()一次，并且可以使用一个索引(如果存在的话)来加载与谓词条件匹配的行。

Where a.DateValue > DateAdd(day,-3,getdate())

一个地方。DateValue >返回(天,3,获取当前日期())

doing this in this way makes your query predicate SARG-able

这样做可以使查询谓词SARG-able

#2

Microsoft's documentation at http://msdn.microsoft.com/en-us/library/aa258269%28v=sql.80%29.aspx suggests that instead of DateTimeNow you should have getdate(). Does it work any better that way?

微软在http://msdn.microsoft.com/en- us/library/aa258269.28 % =sql.80%29.aspx上的文档表明，应该使用getdate()而不是DateTimeNow。这样更好吗?

#3

Your query doesn't seem to bad. Another way to tackle it would be:

你的问题似乎还不错。另一种解决办法是:

SELECT * FROM a WHERE a.DateValue > DATEADD(dd,-3,GETDATE())

#1