Why to_date doesn't work when results are filtered to match my date format (Oracle SQL)

Oracle (and other databases) are not obligated to evaluate predicates applied to inline views before evaluating outer predicates. In fact, often from a performance optimization standpoint, you want the optimizer to push select predicates from the outer query into the view, inline view, or subquery. In this case, whether the query throws an error will depend on the query plan chosen by the optimizer and which predicates it evaluates first.

As a quick trick, you can change the inline view to prevent the predicate from being pushed. In this case, the presence of a rownumprevents the optimizer from pushing the predicate. You can also use hints like no_push_predtrying to force the optimizer to use the plan you want

select *
from (  select t.*, rownum rn
        from tbl t
        where col <> 'A')
where to_Date(col,'DD/MM/YYYY') = to_date('01/01/2020','DD/MM/YYYY');

The problem with these two quick hacks, though, is that some future versions of the optimizer may have more options than you realize today, so you may run into problems in the future.

A better option is to rewrite the query so that you don't have to worry about the evaluation order of the predicates. In this case (depending on the Oracle version) this is very simple as it to_dateallows you to specify a value in the event of a conversion error

select *
from tbl
where col <> 'A'
and to_Date(col default null on conversion error,'DD/MM/YYYY') = 
      to_date('01/01/2020','DD/MM/YYYY');

If you're using an earlier version of Oracle, or to_datejust an example of a real problem, you can create a custom function that does the same thing.

create function safe_to_date( p_str in varchar2, p_fmt in varchar2 )
  return date
is
begin
  return to_date( p_str, p_fmt );
exception
  when value_error
  then
    return null;
end safe_to_date;
select *
from tbl
where col <> 'A'
and safe_to_date(col,'DD/MM/YYYY') = to_date('01/01/2020','DD/MM/YYYY');