Jura Jura - 3 months ago 6
SQL Question

Find out what data caused an Oracle error

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

Setup:

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.
Comments