Find out what data caused an Oracle error

Question

I'm trying to run a select statement in SQL Developer that casts a field like 20160809 to a date. My query looks like:

select
  to_date(sale_date, 'YYYYMMDD') as sale_date
from properties

This is throwing an ORA-01839: date not valid for month specified. I've tried everything from substringing to regular expressions to try to deduce which value is causing the error, but no dice. Is there any way to execute this query and get the input to to_date that's causing it to fail?

Answer 1

Create the table:

create table properties(sale_date varchar2(8));
insert into properties values ('20160228');
insert into properties values ('20160230'); 
insert into properties values ('xxxx');

If your table is not too big, you can try this:

declare
      d date;
  begin
      for i in (select * from properties) loop
          begin
              d:= to_date(i.sale_date, 'yyyymmdd');
          exception
              when others then
                  dbms_output.put_line('KO: "' || i.sale_date || '"');
          end;
      end loop;
  end;

OUTPUT:

KO: "20160230"
KO: "xxxx"