SQL Server Performance Forum – Threads Archive

DateDiff versus comparison operators

Hi, I need to compare to see if date1 is less than date2. I can either use < or DateDiff. I know either will work, but I am curious to know if one is preferred over the other. Btw – they came up 50/50 in execution plan when compared. Any insight is appreciated. Thanks,
Eric

---

In WHERE statements, avoid doing calculations on date columns. If the date column is indexed, you may get an index scan at best, but never an index seek. For instance this:
WHERE DATEDIFF(DAY, date_column, GETDATE()) < 100 can also be written as:
WHERE date_column BETWEEN DATEADD(DAY, -100, GETDATE()) AND GETDATE()

---

Got it, thanks. Let SQL do the work in comparing the dates.

---

]]>