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 |
|
Ashkar
Starting Member
6 Posts |
 Posted - 2011-09-20 : 09:03:16
|
| Hi Friends, I am a new born baby in SQL, Could anyone help me to create a query which is used for purgin any data older than 1 year (365 days) from the current date, eg: my SQL databse name is 'Plima', table is 'warehouse' in the table 'warehouse' I have a column named dateofconnect and its value should read like 2011.09.20I have googled many sites for a solution but I have not got one !! (may be I am quite new with SQL queries) I would highly appreciate if someone can help me by creating such query, It should always calculate the current date and delete the data which is morethan a year, Thanking you in advance Kind RegardsAshkar Dubai |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-20 : 09:49:38
|
| [code]DECLARE @dt datetimeSET @dt = DateAdd(day, -365, getdate())DELETE warehouseWHERE dateofconnect < @dt;[/code] |
 |
|
|
Ashkar
Starting Member
6 Posts |
Posted - 2011-09-21 : 09:09:56
|
| Thanks Russel, I have tested this with select statement, and it shows accurate reply, One more question, Can I use this query in job schedule for off peak hours on a weekly basis ? Thanks |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-21 : 09:58:48
|
| Definitely. That's the best way to do it. |
|
|
|
Ashkar
Starting Member
6 Posts |
Posted - 2011-09-21 : 10:17:09
|
| Russel You are the BO$$ :-)Thank you very much |
|
|
|
|
|
|
Automatic Purging