Search code examples
sqloracle-databasedatedate-comparison

ORA-01843: not a valid month error

I have a column in Oracle DB which is varchar2 data type. Typical value stored in this column is like 06/16/2015 02:14:18 AM. I am trying to get all records wherein this column is having records after 1st August 2015.

select * 
from MYTABLE 
where to_date(substr(MYCOLUMN,1,10),'dd-mm-yyyy') > to_date('01-08-2015','dd-mm-yyyy');

But, I am getting ORA-01843. Where am I doing wrong?

Solution

  • Respect the format in your VARCHAR

    ....where to_date(substr(MYCOLUMN,1,10),'mm/dd/yyyy')