All Forums UDA
Coolguyz 3 posts Joined 11/09
19 Nov 2009
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 AMI wante to comapre column A with dates so that I can delete the records older than (Sysdate - 30).Any help?

Jimm 298 posts Joined 09/07
19 Nov 2009

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 - 30or Select Substr(col1,1,10) (Date, Format 'dd/mm/yyyy')Where Date LT Current_Date - 30

Coolguyz 3 posts Joined 11/09
19 Nov 2009

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

Coolguyz 3 posts Joined 11/09
19 Nov 2009

Thank you very much!I was able to compare the dates using below,to_date(substr(column,1,10),'mm/dd/yyyy') < '30Oct2009'

Jimm 298 posts Joined 09/07
20 Nov 2009

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')

You must sign in to leave a comment.