SQL Server Performance Forum – Threads Archive
DateDiff versus comparison operatorsHi, 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,
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.