Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
asad.lte
Starting Member
32 Posts |
 Posted - 2011-05-09 : 07:19:44
|
| i hav requirement to delete the records from a table on a monthly basis,for this using loop what i have asked is to get the min date from the table n delete basing on that keeping current month data plus 5 days data,so that the table shud hav some data after deleting,support me thanx in advance |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-05-09 : 07:26:23
|
| DELETE myTABLEWHERE dateColumn<DATEADD(day, -5, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) |
 |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2011-05-09 : 07:28:18
|
| DELETE FROM yourtableWHERE DATEDIFF(D, (RecordInsertionDate), GETDATE()) BETWEEN 0 AND 35--------------------------http://connectsql.blogspot.com/ |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-05-09 : 07:31:01
|
| I think that's the inverse of what they wanted to keep (although I admit the question is not clearly worded) |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2011-05-09 : 07:35:20
|
| ooooops sorry, you are right robvolkcorrect version is here (but prefer robvolk query)DELETE FROM yourtableWHERE DATEDIFF(D, (RecordInsertionDate), GETDATE()) >= 35--------------------------http://connectsql.blogspot.com/ |
|
|
|
|
|
|
|
|
query with loop to delete the records