I have VARCHAR2 DateTime string. I want to convert it into DateTime and compare it with DateTime in the where clause.
to_Date(VARCHAR2 field, 'MM/DD/YYYY HH:MI:SS AM')>= '09/10/2008 08:31:10 AM'
If I just do SELECT to_Date(VARCHAR2 field, 'MM/DD/YYYY HH:MI:SS AM') FROM Table;- it works.
But when using it in the where clause it gives an error. -ORA-01843: not a valid Month
Make sure you have consistent datatypes on *both* sides of the predicate
SQL> create table t ( x varchar2(100));
Table created.
SQL> insert into t values ('03/27/2018 12:34:12');
1 row created.
SQL> select count(*) from t
2 where to_Date(x, 'MM/DD/YYYY HH:MI:SS AM')>= '09/10/2008 08:31:10 AM';
where to_Date(x, 'MM/DD/YYYY HH:MI:SS AM')>= '09/10/2008 08:31:10 AM'
*
ERROR at line 2:
ORA-01843: not a valid month
SQL> select count(*) from t
2 where to_Date(x, 'MM/DD/YYYY HH:MI:SS AM')>= to_Date('09/10/2008 08:31:10 AM','MM/DD/YYYY HH:MI:SS AM');
COUNT(*)
----------
1
and of course... ideally, try move away from storing dates as varchar2. That always causes problems.