Oracle BUG invalid julianday

如果ORACLE遇到非法或异常的时间类型数据时，在某些特定的情况下将其自动转换为rsquo;0000-00-00rsquo;而存储下来，不会抛出异

在一次datastage做etl，需要将一个source库的tab1数据导入到目标库tab2，两张表结构相同，发现etl执行报错invalid julian day

如果Oracle遇到非法或异常的时间类型数据时，在某些特定的情况下将其自动转换为’0000-00-00’而存储下来，不会抛出异常或错误提示。

比如在PL/SQL下，正常情况是不允许 年份为0值的：

SQL> select to_date('0000-01-01','yyyy-mm-dd') from dual;

 

select to_date('0000-01-01','yyyy-mm-dd') from dual

 

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

但如果使用DATE函数，ORACLE将不会给出错误提示，而自动转换为‘0000-00-00‘：

SQL> select date '0000-01-01' from dual;

 

DATE'0000-01-01'

----------------

0/0/0000

这时如果使用DATE函数处理后来插入或更新这类日期值时，系统是不会给出错误提示，这在9i和10G下都是同样的处理方式。

还有另外两种情况，在PL/SQL下也会出现这样的问题：

1.     如果日期表达式的结果小于或等于0，结果都为'0000/00/00'：

SQL> select to_date('0001-01-01', 'yyyy-mm-dd')-720 from dual;

 

TO_DATE('0001-01-01','YYYY-MM-

------------------------------

0/0/0000

 

SQL> select to_date('0001-01-01', 'yyyy-mm-dd')-365 from dual;

 

TO_DATE('0001-01-01','YYYY-MM-

------------------------------

0/0/0000

 

2.     对100到1500年之内的所有整百年的日期进行计算，如果结果为2月29的话，结果都为'0000/00/00'：

SQL> select date '0099-2-28' +1 from dual;

 

DATE'0099-2-28'+1

-----------------

3/1/0099

 

SQL> select date '0100-2-28' +1 from dual;

 

DATE'0100-2-28'+1

-----------------

0/0/0000

 

在其它语言的处理上，也有类似的情况。而且在目前所有的ORACLE版本中，都没有对这类的错误修正，，所以在进行日期类型的值做转换和处理时，应该注意其转换后值的合法性和有效性。

后来处理还发现

有问题的日期用函数to_char的话会出现两种值，最后通过这两个值找到了问题数据，将错误日期改掉才解决~
to_char(DISABLE_DATE,'yyyy-MM-dd') = '3593-11-30'
or  to_char(CLMDOC_READY_DATE,'yyyy-MM-dd') = '3593-11-30'
or  to_char(DEATH_DATE,'yyyy-MM-dd') = '3593-11-30'
or  to_char(DIAGNOSE_DATE,'yyyy-MM-dd') = '3593-11-30'
or  to_char(DELAY_DATE,'yyyy-MM-dd') = '3593-11-30'
or  to_char(FCD,'yyyy-MM-dd') = '3593-11-30'
or  to_char(LCD,'yyyy-MM-dd') = '3593-11-30'
or
to_char(DISABLE_DATE,'yyyy-MM-dd') = '0000-00-00'
or  to_char(CLMDOC_READY_DATE,'yyyy-MM-dd') = '0000-00-00'
or  to_char(DEATH_DATE,'yyyy-MM-dd') = '0000-00-00'
or  to_char(DIAGNOSE_DATE,'yyyy-MM-dd') = '0000-00-00'
or  to_char(DELAY_DATE,'yyyy-MM-dd') = '0000-00-00'
or  to_char(FCD,'yyyy-MM-dd') = '0000-00-00'
or  to_char(LCD,'yyyy-MM-dd') = '0000-00-00'
)
;