SQL DateDifference in a where clause

sql sql-server datetime projection
DarthVader picture DarthVader · Mar 10, 2011 · Viewed 42.2k times · Source

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..

Answer

Charles Bretana picture Charles Bretana · Mar 10, 2011

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

Related questions

How to return only the Date from a SQL Server DateTime datatype

SELECT GETDATE() Returns: 2008-09-22 15:24:13.790 I want that date part without the time part: 2008-09-22 00:00:00.000 How can I get that?

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More
How to convert DateTime to VarChar

I am working on a query in Sql Server 2005 where I need to convert a value in DateTime variable into a varchar variable in yyyy-mm-dd format (without time part). How do I do that?

Read More