SQL Server Performance Forum – Threads Archive

Delete query based on moving date

I need to setup a query that will delete records older than 90 days. Currently, i have this code: —code— DELETE
FROM myTable
WHERE dtDate < ‘7/1/2004’ —end code— how can i make the ‘7/1/2004’ a moving date based on today’s date? Thanks,
John Regards, John Hobby
SQL Beginner
Tennessee

---

Look at DATEDIFF and GETDATE() in Books Online. Tom Pullen
DBA, Oxfam GB

---

Try This. DELETE
FROM myTable
WHERE dtDate < Convert(Varchar(10),Getdate()-90,1)
Thanks
Sandy

---

With a single "delete from table where dtDate < yourdate" and no index on dtdate, you may end up with your whole table being locked during the table scan for the big single transaction your delete uses.
So, provide and index for your criteria ! You may also want to keep your transactions small and just do a number of small transactions instead of one single bigone.
e.g. :
set nocount on
go
Declare @TsRef datetime
select @TsRef = dateadd(d,-90, getdate())
print ‘TsRef: ‘ + convert(char(26), @TsRef, 121) + char(13)
Declare @Counter int
Set @Counter = 1
Declare @TotCounter int
Set @TotCounter = 0
While @Counter > 0
Begin
Begin Transaction DelTop Delete yourschema.yourtable
FROM (SELECT TOP 1000 primary_key_column(s)
from yourschema.yourtable
where dtDate < @TsRef ) as SelTop
where yourschema.yourtable.primary_key_comlumn(s)= SelTop.primary_key_column(s)
set @Counter = @@rowcount
set @TotCounter = @TotCounter + @Counter Commit Transaction DelTop End
Print ‘–> yourschema.yourtable # Rows Deleted : ‘ + str(@TotCounter )
go

---

]]>