创建表：

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"

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

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

您是否在使用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 ;