sql to delete rows from a table that are more than 3 days old.
I have a table with col1 - varchar2 and col2 - date columns
I want to remove those rows that are more 3 days old.
I don't want to use pl/sql code, I want to do it using sql
I tried using this statement but it failed:
delete from abc where to_date(dt) = to_date(sysdate)-3 ;
shows 0 rows deleted.
I want to remove those rows that are more 3 days old.
I don't want to use pl/sql code, I want to do it using sql
I tried using this statement but it failed:
delete from abc where to_date(dt) = to_date(sysdate)-3 ;
shows 0 rows deleted.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Do u ur dates have a time component to it? E.g. 2-FEB-2008 11:33:54 or just 2-FEB-2008 00:00:00
try this
delete from abc where dt < sysdate -3;
assuming dt is a date field.
it should work
delete from abc where dt < sysdate -3;
assuming dt is a date field.
it should work
try this
delete from abc where dt < sysdate -3;
assuming dt is a date field.
it should work
delete from abc where dt < sysdate -3;
assuming dt is a date field.
it should work
@ysd - Request u to kindly check the previous posts before repeating similar solutions.
ASKER
DT
---------
06-FEB-08
07-FEB-08
08-FEB-08
08-FEB-08
08-FEB-08
08-FEB-08
delete from abc where dt < sysdate -3;
sysdate should be 08-FEB-08
0 rows deleted
---------
06-FEB-08
07-FEB-08
08-FEB-08
08-FEB-08
08-FEB-08
08-FEB-08
delete from abc where dt < sysdate -3;
sysdate should be 08-FEB-08
0 rows deleted
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
My bad :) Get to leafing through these quickly and don't read the whole thing. Thanks awking00!
Open in new window