找出导致Oracle错误的数据

olhwl3o2  于 2022-12-03  发布在  Oracle
关注(0)|答案(3)|浏览(96)

我尝试在SQL Developer中运行select语句,该语句将20160809之类的字段转换为date。我的查询如下所示:

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

这是一个ORA-01839: date not valid for month specified。我尝试了从子串到正则表达式的所有方法来推断哪个值导致了错误,但是没有任何结果。有没有什么方法可以执行这个查询并获得导致它失败的to_date的输入?

mznpcxlj

mznpcxlj1#

创建表:

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

如果您的表不是太大,您可以尝试以下操作:

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;

输出:

KO: "20160230"
KO: "xxxx"
8hhllhi2

8hhllhi22#

我想你可能需要一个蛮力的方法:

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';

这不是100%完美。它留下了闰年错误的可能性。您可以使用以下方法手动调查这些错误:

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

klh5stk13#

您是否在使用Oracle 12c?如果是,那么

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 ;

如果您使用的不是Oracle 12c,则可以将safe_to_date放入一个包中,例如“my_pkg”,然后执行以下操作:

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

相关问题