Search code examples
sql-server-2005datediffsql-delete

Deleting records having a date older than 3 days, for rolling 3 day job?

Prior to executing a sensitive sql command I wanted to do a sanity check.

I am trying to delete records that have a [LoadDt] date value older than 3 days and my code is:

delete IntraDayStats
where DATEDIFF(dd, LoadDt, dateadd(d,-3, getdate()) ) >= 3

I want to schedule this as a sql job so that my IntraDayStats table has a rolling 3 day history. The job will run nightly.

Solution 
  • where DATEDIFF(dd, LoadDt, dateadd(d,-3, getdate()) ) >= 3

    is not sargable (an index won't be used), use

    where LoadDt < getdate()- 3

    Next time if you want to check, make the DELETE a SELECT and see what you get back