query to delete the records for 5 week older data

Question

Post reply

query to delete the records for 5 week older data

Jay Pete

SSCommitted

Points: 1945
More actions
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

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

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

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

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

Jay Pete SSCommitted Points: 1945 More actions · Answer 2

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?

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

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]

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

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.

Jay Pete SSCommitted Points: 1945 More actions · Answer 5

Thanks to Eugene and Anthony for your quick resopnse.