Dear Tom,

I have column (C1) in varchar2 datatype, and it has around 10k records coming as 'dd-mon-yyyy'format (ex:'22-jun-2022').

when querying below is exucuted without error,

select C1,
CASE WHEN TO_DATE(C1,'dd-mon-yyyy') between to_date('01-Jun-2022') and to_date('30-Jun-2022') then 1 else 0 end ---for checking it is giving 1 or 0
from T;

where it as,if I write like below getting error as 'ora-01858 a non-numeric character was found where numeric expected'.

select C1,
CASE WHEN TO_DATE(C1,'dd-mon-yyyy') between to_date() and to_date() then 1 else 0 end
from T
where TO_DATE(C1,'dd-mon-yyyy') between to_date('01-Jun-2022') and to_date('30-Jun-2022');

--also tried

select C1,
CASE WHEN TO_DATE(C1,'dd-mon-yyyy') between to_date() and to_date() then 1 else 0 end
from T
where TO_DATE(C1,'dd-mon-yyyy') between to_date('01-Jun-2022','dd-mon-yyyy') and to_date('30-Jun-2022','dd-mon-yyyy');

In the select list it is not getting any error,but in the where clause it is showing error. Please share if any clue/references?

SQL> with bad_dates as
  2  ( select '35-JAN-2022' d from dual )
  3  select to_date(d,'DD-MON-YYYY') from bad_dates;
select to_date(d,'DD-MON-YYYY') from bad_dates
               *
ERROR at line 3:
ORA-01847: day of month must be between 1 and last day of month

SQL> with bad_dates as
  2  ( select '35-JAN-2022' d from dual )
  3  select to_date(d default null on conversion error,'DD-MON-YYYY') from bad_dates;

TO_DATE(D
---------


1 row selected.