15

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.

SQL server.

How to do this?

DATEDIFF works too slow..

Nishant
  • 20,354
  • 18
  • 69
  • 101
DarthVader
  • 52,984
  • 76
  • 209
  • 300

3 Answers3

37

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.

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.

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

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

Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
  • SARG-able, I had not heard that before. Thanks! – Daniel Williams Mar 10 '11 at 22:42
  • `the query processor must run the function on every row in the table` helped me understand and solve an issue I was having where the exact same DATEDIFF statement would complain about an overflow in the WHERE clause but not in SELECT. – Pier-Luc Gendreau Oct 05 '13 at 19:38
  • I was having troubles with a subquery that use a where with DATEDIFF that makes me crazy, your solution was great and really helped me. thanks you so much. – FabianSilva Sep 23 '14 at 14:44
  • Does it matter if you do the `DateAdd` within the `Where` or assign it to a variable outside? – mbomb007 Jan 05 '18 at 20:55
  • You mean within a SQL Query statement or in a variable created outside the SQL statement but inside a Stored procedure? – Charles Bretana Jan 05 '18 at 21:00
  • Would it be slightly more efficient to use a variable so dateadd and getdate are only called once? Or does SQL know enough to only calculate it once? – Morgan T. Oct 18 '20 at 18:36
  • @Morgan, No, if the DateAdd() expression operates on a variable within the SQL statement, and not on a table column value, then the query processor is smart enough to only execute it once. – Charles Bretana Oct 20 '20 at 16:13
1

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?

Gareth McCaughan
  • 19,888
  • 1
  • 41
  • 62
0

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

SELECT * FROM a WHERE a.DateValue > DATEADD(dd,-3,GETDATE())
cairnz
  • 3,917
  • 1
  • 18
  • 21