SQL Server Performance

DateDiff versus comparison operators

Discussion in 'SQL Server 2005 General Developer Questions' started by escherrer, Sep 12, 2006.

  1. escherrer New Member

    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
  2. Adriaan New Member

    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()

  3. escherrer New Member

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

Share This Page