CODEWITHSUNDEEP
sqloracle-database
Troy Reynolds
Troy Reynolds

Reputation: 68

Delete Records past 6 month Oracle

I am trying to delete all records past 6 months in my table for ORACLE.

I currently have 

delete from table t 
where to_date(substr(t.DATE_SENT,1,10),'MM/DD/YYYY')  < add_months(trunc(sysdate), -6);

The string format in this table is '12/21/2015 12:00:00 AM'.

NOTE I understand that this should be a DATETIME and not a varchar2 but it just happens to be a varchar2 so I am trying to find a work around it for now.

This query seems to somewhat work for select * but then crashes with SQL

Error: ORA-01830: date format picture ends before converting entire input string

Any help would be appreciated.

Upvotes: 1

Views: 3658

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

No need of SUBSTR. directly use TO_DATE on the format.

DELETE FROM t
      WHERE TO_DATE (t.DATE_SENT, 'MM/DD/YYYY HH:MI:SS AM') <
               ADD_MONTHS (TRUNC (SYSDATE), -6);

Upvotes: 2

Related Questions