Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    202

    Unanswered: Delete all data which are more than one week old

    Hi there,

    How to delete all the data which are more than week old in the SQL Server?

    Thanx

  2. #2
    Join Date
    Oct 2003
    Posts
    268
    You must execute the following SQL for every relevant table:

    DELETE FROM TABLE WHERE datediff(day, DateStampColumn, getdate()) > 7

    This requires that you know all the tables you want to delete data from and they all have a date stamp column.

    I suspect this isn't as simple as you wanted but there really is no simpler completely generic way to do this.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    assuming DateStampColumn has an index, when you do this --

    ... where datediff(day, DateStampColumn, getdate()) > 7

    you might force a table scan

    (i have not tested this lately, i don't know how smart the latest optimizer is)

    do this instead --

    ... where DateStampColumn < dateadd(day,-7,getdate())

    this way, the expression on the right is evaluated first, then the resulting value can be compared to the index values

    remember, internally datetime values are full integers
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2004
    Posts
    202
    Thanx fren.
    I think i manage to do it already with the code u gave.
    Thanx again

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Michael,

    Be aware that though these examples use a hypothetical "DateStampColumn", they are NOT refering to a column of datatype "TimeStamp" in your table. A TimeStamp column is sequentially incremented, and is not in any way related to the actual time or date the data was changed. You need to apply these solutions to a DateTime column, and usually the method of setting the DateTime value is trickier than the process of identifying expired records.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Mar 2004
    Posts
    202
    Thanx for your advice.
    I'll sure will look into it.
    Thanx again

  7. #7
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Talking Delete all data which are more than one Month

    Hey, Are you'll spying over there. I have a similar requirement for once a month purging one months data. This is what I've come up with. SPecs: where working from a historical setup of 6 months:

    select * FROM dbo.Temp_table where DATE_TIME < dateadd(day,-161,getdate())



    Date from today back 5 months

    2003-12-01 00:00:01.000

    PS. all other field have been deleted except datetime and where tested before actual delete was run.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •