Post reply

query to delete the records for 5 week older data

  • May 31, 2012 at 8:22 am

    #257232

    Hi I have table which has to delete the 5 week older data.Please help out.

    I would appreciate your time and help.

    I have the DDL script.

    CREATE TABLE test1(

    col1 DATETIME )

    INSERT INTO test1 VALUES('2011-12-01')

    INSERT INTO test1 VALUES('2012-05-30')

    INSERT INTO test1 VALUES('2011-04-30')

    INSERT INTO test1 VALUES('2012-05-01')

    INSERT INTO test1 VALUES('2012-05-15')

    INSERT INTO test1 VALUES('2012-03-03')

    INSERT INTO test1 VALUES('2012-05-30')

    INSERT INTO test1 VALUES('2011-04-30')

    INSERT INTO test1 VALUES('2012-05-01')

    INSERT INTO test1 VALUES('2012-05-15')

    Thanks in advance,

    Jay

  • May 31, 2012 at 8:27 am

    #1495264

    DELETE FROM test1 WHERE col1 <= DATEADD(WEEK,-5,GETDATE())

    Will delete anything where col1 is 2012-04-26 (based on today) or less

  • May 31, 2012 at 8:48 am

    #1495283

    anthony.green (5/31/2012)

    DELETE FROM test1 WHERE col1 <= DATEADD(WEEK,-5,GETDATE())

    Will delete anything where col1 is 2012-04-26 (based on today) or less

    Thanks Anthony.It Works.

    Can we use DATEDIFF function?

  • May 31, 2012 at 8:52 am

    #1495287

    Jay Pete (5/31/2012)

    anthony.green (5/31/2012)

    DELETE FROM test1 WHERE col1 <= DATEADD(WEEK,-5,GETDATE())

    Will delete anything where col1 is 2012-04-26 (based on today) or less

    Thanks Anthony.It Works.

    Can we use DATEDIFF function?

    Yes you can, but why would you want to make it slower (if the table has index on Col1...)?

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • May 31, 2012 at 8:52 am

    #1495288

    Depends what you want to do, DATEDIFF gets the difference between 2 dates, in this case you simply want to subtract 5 weeks from the current value which DATEADD is the function to use.

  • May 31, 2012 at 8:56 am

    #1495291

    Thanks to Eugene and Anthony for your quick resopnse.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply