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?

What is the type of the `sale_date` column? Do you have any invalid dates in there, e.g. August 32? — Tim Biegeleisen, Aug 09 '16 at 15:34
@TimBiegeleisen: it's a `varchar2` field. I'm not sure ift there are invalid dates. Was hoping to see what input to `to_date` is breaking it. — serverpunk, Aug 09 '16 at 15:43
I would start with `select to_date from properties where length(to_date) != 8`. Rule those out first. If, in a different era, the dates were held as numbers and later they were converted to strings, that could have introduced all sorts of errors. — , Aug 09 '16 at 15:49

score 3 · Accepted Answer · edited Dec 01 '22 at 05:33

3

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"

edited Dec 01 '22 at 05:33

ShirinJZ

107
2
16

answered Aug 09 '16 at 15:42

Aleksej

22,443
5
33
38

This was perfect, though I needed to turn on `dbms_output` in SQL Developer: http://stackoverflow.com/questions/7887413/printing-the-value-of-a-variable-in-sql-developer – serverpunk Aug 09 '16 at 15:56

score 2 · Answer 2 · answered Aug 09 '16 at 15:37

I think you might need a brute force method:

select sale_date
from properties 
where substr(sale_date, 5, 4) not between '0101' and '0131' and
      substr(sale_date, 5, 4) not between '0201' and '0228' and
      substr(sale_date, 5, 4) not between '0301' and '0431' and
      substr(sale_date, 5, 4) not between '0401' and '0430' and
      substr(sale_date, 5, 4) not between '0501' and '0531' and
      substr(sale_date, 5, 4) not between '0601' and '0630' and
      substr(sale_date, 5, 4) not between '0701' and '0731' and
      substr(sale_date, 5, 4) not between '0801' and '0831' and
      substr(sale_date, 5, 4) not between '0901' and '0930' and
      substr(sale_date, 5, 4) not between '1001' and '1031' and
      substr(sale_date, 5, 4) not between '1101' and '1130' and
      substr(sale_date, 5, 4) not between '1201' and '1231';

This isn't 100% perfect. It leaves open the possibility of leap year errors. You can investigate those manually with:

select sale_date
from properties 
where sales_date like '%0229';

There were two invalid leap year dates! This was helpful, thank you. — serverpunk, Aug 09 '16 at 15:59

score 2 · Answer 3 · answered Aug 09 '16 at 15:41

Are you on Oracle 12c? If so, then

with function safe_to_date(p_string VARCHAR2, p_format VARCHAR2) RETURN DATE IS
BEGIN 
  return to_date(p_string, p_format);
EXCEPTION
  WHEN others THEN
    return NULL;
END;
select sale_date from properties
where sale_date is not null and safe_to_date(sale_date,'YYYYMMDD') IS NULL ;

If you are not on Oracle 12c, you can put safe_to_date into a package, say "my_pkg" and then this:

select sale_date from properties
where sale_date is not null and my_pkg.safe_to_date(sale_date ,'YYYYMMDD') IS NULL ;

I'm on 11g, but I'll look into putting it into a package. Thanks! — serverpunk, Aug 09 '16 at 15:45

Find out what data caused an Oracle error

3 Answers3