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 |
|
admin001
Posting Yak Master
166 Posts |
 Posted - 2002-08-07 : 08:32:32
|
| Hello ,I had posted a query to delete all records from a table older than 90 days .Wanted to know further if i could do it by setting up a job through enterprise manager and use the truncate table command in the step menu and configure the time schedule accordingly rather than using a complex query .Thanks |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-08-07 : 08:40:57
|
| You can't do a "partial" truncate - its all or nothingA simple weekly job with a TSQL step that runsdelete MYTABLEwhere datediff(dd,date_col,getdate())>90will do just fineHTHJasper Smith |
 |
|
|
admin001
Posting Yak Master
166 Posts |
Posted - 2002-08-07 : 12:47:05
|
| Hi ,Thanks for the reply . Is the date_col the column which is imported during the import task of the log files which has dates in it or it is timestamp column i have added to the table which has a getdate function ? I guess the date_col stands for start date . How do i define the start date in this case ?Thanks once again . |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-08-07 : 12:54:58
|
quote:
delete all records from a table older than 90 days
Whichever column defines that a row is older than 90 days - that is the one you want to use in place of date_col.HTHJasper Smith |
|
|
|
admin001
Posting Yak Master
166 Posts |
Posted - 2002-08-20 : 09:49:54
|
| Hello ,Extremely thanks for the solution . But i am little confused in writing the exact query i.e filling the correct details in the query .I am getting some different results . To simplify let me explain....I have a table now with loadate column which gets the default date and time . The loadate shows correct date and time when the data was imported in the table . Now suppose if i want to delete previous 5 days records from today ( for e.g today is 20/08/2002 3:40:00 PM ) ideally it should delete all records which are 5 days older from today . i.e from 20/08/2002 3:40:00 PM to 15/08/2002 3:40:00 PM ) But when i execute the datediff command , it deletes the records previous than 15/08/2002 till 15/08/2002. The records from 15/08/2002 to 20/08/2002 remain intact . Am i missing something in the query or i am confused about the calculation of the dates the datediff command performs . I am using the query as delete MYTABLE where datediff(dd,date_col,getdate())>5 i have replaced the date_col column with loaddate column . Is the logic correct or i am missing someting important ? Thanks once again . |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-08-20 : 10:26:25
|
| you're requirements don't match what you are saying....or what we understand them to be...using your example....."for e.g today is 20/08/2002 3:40:00 PM ) ideally it should delete all records which are 5 days older from today . i.e from 20/08/2002 3:40:00 PM to 15/08/2002 3:40:00 PM"MOST of us would NOT consider this statement true. Think of it...something created at "20/08/2002 3:39:00 PM" is only 1 minute old....and THEREFORE does NOT qualify as being OLDER (or in existance) than 5 days."it deletes the records previous than 15/08/2002" is exactly what you asked for....and got.what you seem to be suggesting you want is anything created IN the last 5 days....a different requirement. |
|
|
|
|
|
|
Delete Jobs after 90 days !!