CODEWITHSUNDEEP
oracle-databasesysdate
Gennady Sorochan
Gennady Sorochan

Reputation: 13

Delete from Oracle table rows older than 1 month

In Oracle I have a table with a DB_FLD_4 defined as VARCHAR2(20 BYTE) but actually holds DATE information with looks like this:

select DB_FLD_4 from DM_SUPDES_DISTRIB order by DB_FLD_4 desc;

4/9/2017
4/7/2017
4/6/2017

Kind of m/d/yyyy format.

I would like to create SP that would delete every day rows from the table older than 1 month.

My sysdate looks like this:

select sysdate from dual;

SYSDATE
24-APR-17 04.41:00

Please help to manipulate formats to make this possible.

Many thanks !!!

Upvotes: 1

Views: 4320

Answers (1)

JohnHC
JohnHC

Reputation: 11205

Use to_date() on your column, then trunc the sysdate to "round" to the day

delete
from MyTable
where to_date(MyColumn, 'MM/DD/YYYY') < add_months(trunc(sysdate), -1)

Upvotes: 2

Related Questions