SQL> c/1999-12-12/1999-01-12
1* select to_date('1999-01-12','yyyy-mm-dd') - to_date('1999-12-01','yyyy-mm-dd') from dual
SQL> /
TO_DATE('1999-01-12','YYYY-MM-DD')-TO_DATE('1999-12-01','YYYY-MM-DD')
---------------------------------------------------------------------
-323
-- 也可以为负数的
SQL> c/1999-01-12/2999-10-12
1* select to_date('2999-10-12','yyyy-mm-dd') - to_date('1999-12-01','yyyy-mm-dd') from dual
SQL> /
TO_DATE('2999-10-12','YYYY-MM-DD')-TO_DATE('1999-12-01','YYYY-MM-DD')
---------------------------------------------------------------------
365193
下面看看INTERVAL YEAR TO MONTH怎么用.
SQL> create table bb(a date, b date, c interval year(9) to month);
Table created.
SQL> desc bb;
Name Null Type
----------------------------------------- -------- ----------------------------
A DATE
B DATE
C INTERVAL YEAR(9) TO MONTH
SQL> insert into bb values(to_date('1985-12-12', 'yyyy-mm-dd'), to_date('1984-12-01','yyyy-mm-dd'), null)
1 row created.
SQL> select * from bb;
A B
--------- ---------
C
---------------------------------------------------------------------------
12-DEC-85 01-DEC-84
SQL> update bb set c = numtoyminterval(a-b, 'year');
1 row updated.
SQL> select * from bb;
A B
--------- ---------
C
---------------------------------------------------------------------------
12-DEC-85 01-DEC-84
+000000376-00
-- 直接将相减的天变成年了, 因为我指定变成年的
SQL> select a-b, c from bb;
A-B
----------
C
---------------------------------------------------------------------------
376
+000000376-00