Oracle时间类型数据为0的Bug

开发组在数据迁移时，报告发现一些数值为'0000/00/00'的date数据，导致数据迁移失败。

这个问题有点奇怪，因为在Oracle中，date类型的数据的取值范围是从-4712/12/31到9999/12/31之间，并且年份不能为0。也就是说'0000/00/00'是一个非法数据，不为Oracle所接受。

 

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

 

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

 

ORA-01843: not a valid month

 

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

 

但为什么在数据中还是出现了'0000/00/00'呢？对此问题稍微研究了一下，发现Oracle在date类型的数据问题上确实存在一些bug，通过一些特殊方法还是能使date类型存储'0000/00/00'数据。先看以下操作，这是bug之一。

 

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

 

DATE'0000-01-01'

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

0/0/0000

 

SQL> select date '0000-11-22' from dual;

 

DATE'0000-11-22'

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

0/0/0000

 

在使用date关键字时，时间格式是罗马格式。此时，我们发现Oracle没有对年份是否为0进行校验。并且，只要年份为0，数据都会被转变为'0000/00/00'。

 

再看另外一种情况，

 

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

 

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

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

0/0/0000

 

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

 

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

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

0/0/0000

 

可以看到，Oracle对时间表达式的结果也没有校验年份是否为0，结合上面的bug，只要计算结果年份为0，无论月、日数值，结果都为'0000/00/00'。

 

再看第三种情况，就更加特殊了：只要对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

 

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

 

DATE'1000-2-28'+1

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

0/0/0000

 

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

 

DATE'1000-2-28'+2

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

3/1/1000

 

SQL> select date '1000-2-27' +2 from dual;

 

DATE'1000-2-27'+2

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

0/0/0000

 

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

 

DATE'1500-2-28'+1

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

0/0/0000

 

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

 

DATE'1600-2-28'+1

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

2/29/1600

 

最后一种情况，如果日期表达式的结果小于0，结果都为'0000/00/00'。

 

SQL> select date '-0001-11-11' +1 from dual;

 

DATE'-0001-11-11'+1

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

0/0/0000

 

SQL> select date '-4712-11-11' +15 from dual;

 

DATE'-4712-11-11'+15

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

0/0/0000

 

SQL> select date '-1111-10-11' +0 from dual;

 

DATE'-1111-10-11'+0

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

0/0/0000

 

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

 

TO_DATE('01/01/01','YYYY/MM/DD

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

0/0/0000

 

顺便要说的是，以上结果不仅可以被查询出来，而且也能被存储在字段类型为date的表中。