Jura Jura - 1 year ago 49
SQL Question

Find out what data caused an Oracle error

I'm trying to run a

statement in SQL Developer that casts a field like
to a
. My query looks like:

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
that's causing it to fail?

Answer Source


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:

SQL> declare
  2      d date;
  3  begin
  4      for i in (select * from properties) loop
  5          begin
  6              d:= to_date(i.sale_date, 'yyyymmdd');
  7          exception
  8              when others then
  9                  dbms_output.put_line('KO: "' || i.sale_date || '"');
 10          end;
 11      end loop;
 12  end;
 13  /
KO: "20160230"
KO: "xxxx"

PL/SQL procedure successfully completed.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download