Datediff performance

sql-server tsql datediff
Murtaza Mandvi picture Murtaza Mandvi · Jan 24, 2013 · Viewed 7.5k times · Source

I have a number of days variable which I want to compare against a datetime column (senddate) .

Im currently doing this :

DECLARE @RunDate datetime = '2013-01-01' 
DECALRE @CalculationInterval int = 10

DELETE
FROM TableA
WHERE datediff(dd, senddate, @RunDate) > @CalculationInterval

So basically anything that is older then 10 days should get deleted, we have Index on sendDate column but still the speed is much slower, I know the left side should not have calculation for performance reasons, but what is the optimal way of otherwise solving this issue?

Answer

Mitch Wheat picture Mitch Wheat · Jan 24, 2013

The expression

WHERE datediff(dd, senddate, @RunDate) > @CalculationInterval

won't be able to use an index on the senddate column, because of the function on the column senddate

In order to make the WHERE clause 'SARGable' (i.e. able to use an index), change to the equivalent condition:

WHERE senddate < dateadd(dd, -@CalculationInterval, @RunDate)

[Thanks to @Krystian Lieber, for pointing out incorrect condition ].

Related questions

How to convert number of minutes to hh:mm format in TSQL?

I have a select query that has DURATION column to calculate number of Minutes . I want to convert those minutes to hh:mm format. Duration has values like 60, 120,150 For example: 60 becomes 01:00 hours 120 becomes 02:00 hours 150 becomes 02:30 hours Also, this is how …

Read More
using DateDiff to find duration in minutes

I am trying to use Datediff to find out the duration between columnA and columnB. SELECT datediff (minute, stime, etime) from Exceptions2 where stime = [exceptions2].starttime and etime = [exceptions2].endtime This produces errors. Can anyone please help me with what …

Read More
how to get the number on months between two dates in sql server 2005

I have a column in my sql server 2005 table that should hold the number of months an employee has been in service. Since I also have the date the employee was engaged, I want the "months_In_Service" column to …

Read More