locked
How to delete specific data on specific days RRS feed

  • Question

  • Hi All,

    I am trying to automate a process of only keeping the most recent data and deleting the data that I don't need.

    Below is an example on this date delete this data.

    So today (June 2nd) I deleted all data earlier than May 30th.  2 Weeks from now on June 16th I will need to delete all data before June 14th, on June 30th I'll need to delete all data before June 28th, etc..

    June 2 delete before May 30
    June 16 delete before June 14th
    June 30 delete before June 28th
    July 14 delete before July 12th
    July 28 delete before July 26th

    So basically every two weeks on the Tuesdays I am deleting the previous two weeks data (Sundays to Saturdays)

    How can I go about doing this?

    Any assistance will be greatly appreciated.

    Thanks in advance.

    Tuesday, June 2, 2020 11:31 AM

Answers

  • Consider the next script (daily):

    set dateformat ymd
    
    declare @today as date = GETDATE()
    
    declare @d_day as date = DATEADD(d, -DATEDIFF(d, '2020-06-02', @today) % 14, @today)
    
    declare @d as date = DATEADD(d, -2, @d_day)
    
    -- display values for debugging
    select @today, @d_day, @d
    
    -- delete data
    -- delete * from MyTable where SomeDateColumn < @d

    To verify it, comment the DELETE statement, uncomment SELECT and assign some future test dates to @today variable (instead of GETDATE()).





    • Edited by Viorel_MVP Tuesday, June 2, 2020 12:42 PM
    • Marked as answer by spittingfire Tuesday, June 2, 2020 3:00 PM
    Tuesday, June 2, 2020 12:02 PM
  • A scheduled job on Tuesdays with the following T-SQL will do:

    delete  from yourtable
    where yourdate < dateadd(day,datediff(day,0,getdate())-2,0)

    • Marked as answer by spittingfire Tuesday, June 2, 2020 3:00 PM
    Tuesday, June 2, 2020 2:32 PM

All replies

  • Consider the next script (daily):

    set dateformat ymd
    
    declare @today as date = GETDATE()
    
    declare @d_day as date = DATEADD(d, -DATEDIFF(d, '2020-06-02', @today) % 14, @today)
    
    declare @d as date = DATEADD(d, -2, @d_day)
    
    -- display values for debugging
    select @today, @d_day, @d
    
    -- delete data
    -- delete * from MyTable where SomeDateColumn < @d

    To verify it, comment the DELETE statement, uncomment SELECT and assign some future test dates to @today variable (instead of GETDATE()).





    • Edited by Viorel_MVP Tuesday, June 2, 2020 12:42 PM
    • Marked as answer by spittingfire Tuesday, June 2, 2020 3:00 PM
    Tuesday, June 2, 2020 12:02 PM
  • A scheduled job on Tuesdays with the following T-SQL will do:

    delete  from yourtable
    where yourdate < dateadd(day,datediff(day,0,getdate())-2,0)

    • Marked as answer by spittingfire Tuesday, June 2, 2020 3:00 PM
    Tuesday, June 2, 2020 2:32 PM
  • Thanks Viorel,

    This worked and much appreciated.

    Tuesday, June 2, 2020 2:59 PM
  • Thanks Jingyang Li

    Very simple and also works well.

    Much appreciated

    Tuesday, June 2, 2020 3:00 PM