How to compare date in Varchar2(25 Byte) with dates

UDA

How to compare date in Varchar2(25 Byte) with dates

I have a column "A" of type Varchar2(25 Bytes) which is getting data stored in below format,
10/08/2009 12:04:23 AM

I wante to comapre column A with dates so that I can delete the records older than (Sysdate - 30).

Any help?
4 REPLIES
Enthusiast

Re: How to compare date in Varchar2(25 Byte) with dates

I assume your date is dd/mm/yyyy - modify this if it is mm/dd.

Select Substr('10/08/2009 12:04:23 AM',1,10) (Date, Format 'dd/mm/yyyy')
Where Date LT Current_Date - 30

or Select Substr(col1,1,10) (Date, Format 'dd/mm/yyyy')
Where Date LT Current_Date - 30

Re: How to compare date in Varchar2(25 Byte) with dates

Any idea how should one use the same query in Oracle SQl developer?

Re: How to compare date in Varchar2(25 Byte) with dates

Thank you very much!

I was able to compare the dates using below,

to_date(substr(column,1,10),'mm/dd/yyyy') < '30Oct2009'
Enthusiast

Re: How to compare date in Varchar2(25 Byte) with dates

Be careful. I am not sure whether Oracle will do the compare as dates or characters.

I would use:

to_date(substr(column,1,10),'mm/dd/yyyy') < to_date('30Oct2009','ddmmmyyyy')