locked
How to delete specific data on specific days RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks Viorel,

    This worked and much appreciated.

    Tuesday, June 2, 2020 2:59 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks Jingyang Li

    Very simple and also works well.

    Much appreciated

    Tuesday, June 2, 2020 3:00 PM