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

sql-server-2005 datediff sql-delete
kacalapy picture kacalapy · Jan 3, 2011 · Viewed 7.8k times · Source

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.

Answer

SQLMenace picture SQLMenace · Jan 3, 2011 
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

Related questions

How to compare two dates to find time difference in SQL Server 2005, date manipulation

I have two columns: job_start job_end 2011-11-02 12:20:37.247 2011-11-02 13:35:14.613 How would it be possible using T-SQL to find the raw amount of time that has passed between when the job started and when the job ended? I …

Read More
SQL Server 2005: how to subtract 6 month

I have a date, suppose today date declare @d datetime set @d = '20101014' I need select @d - <six month> where is the real number of days that contains last six month, beginning from @d.

Read More
Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More