Which is better to compare dates < or DateDiff?

date, datetime, sql-server, t-sql

Solution

The best will likely be:

SELECT <column list> -- not * (1)
  FROM dbo.TableA -- please always specify schema (2)
  WHERE productDate < '20131209'; -- always use a semi-colon (3)
    -- and always use a non-regional, unambiguous date format (4)

The reason this will be best is because it gives the optimizer the best chance to use an index on the `productDate` column. Even if an index doesn't exist today, someone may add one tomorrow. Applying functions like `DATEDIFF()` to the column will always make the expression non-sargable, meaning it will always have to use a less efficient scan (assuming that's the only search predicate).

As for the inline comments:

Bad habits to kick : using SELECT * / omitting the column list

Bad habits to kick : avoiding the schema prefix

Ladies and gentlemen, start your semi-colons!

Bad habits to kick : mis-handling date / range queries

Problem

Which is considered better? ``` select * from TableA where productDate < '12/9/2013' ``` or ``` select * from TableA where DATEDIFF(day, productDate, '12/9/2013') > 0 ``` While skimming thru articles, I read that using Date functions (ex: datediff, dateadd) in where clauses affected performance negatively. Is this true?

Original source