If you have a query and need to calculate the difference between two dates (or datetimes) using the standard comparison operators (<, >, ==, !=, <=, and >=) is the best way to do it.
Using standard comparison operators gives the optimizer the best chance to use an index on the date column. In contrast, using DATEDIFF() function will result in a less efficient scan.
I would recommend against using the following code if possible:
SELECT column_list
FROM TableA
WHERE datediff (day, (Modified_Date), getdate ()) > 90
Instead, use this code;
SELECT column_list
FROM TableA
WHERE Modified_Date < (getdate () – 90);
Let’s look at this in action:
1)Second Query’s Execution Plan:
SELECT CustomerID, PersonID ,StoreID ,TerritoryID ,AccountNumber ,ModifiedDate
FROM AdventureWorks2014.Sales.Customer
WHERE ModifiedDate < (getdate () – 90);
2)First Query’s Execution Plan:
SELECT CustomerID, PersonID ,StoreID ,TerritoryID ,AccountNumber ,ModifiedDate
FROM AdventureWorks2014.Sales.Customer
WHERE datediff (day, (ModifiedDate), getdate ()) > 90
As you can see above execution plan results, comparison operators perform better for calculating the difference between two dates.
