更新以纠正错误。最初由@jen回答。
with DATES as (
select TO_DATE('20120101', 'YYYYMMDD') as Date1,
TO_DATE('20120325', 'YYYYMMDD') as Date2
from DUAL union all
select TO_DATE('20120101', 'YYYYMMDD') as Date1,
TO_DATE('20130101', 'YYYYMMDD') as Date2
from DUAL union all
select TO_DATE('20120101', 'YYYYMMDD') as Date1,
TO_DATE('20120101', 'YYYYMMDD') as Date2
from DUAL union all
select TO_DATE('20130228', 'YYYYMMDD') as Date1,
TO_DATE('20130301', 'YYYYMMDD') as Date2
from DUAL union all
select TO_DATE('20130228', 'YYYYMMDD') as Date1,
TO_DATE('20130401', 'YYYYMMDD') as Date2
from DUAL
), MONTHS_BTW as (
select Date1, Date2,
MONTHS_BETWEEN(Date2, Date1) as NumOfMonths
from DATES
)
select TO_CHAR(Date1, 'MON DD YYYY') as Date_1,
TO_CHAR(Date2, 'MON DD YYYY') as Date_2,
NumOfMonths as Num_Of_Months,
TRUNC(NumOfMonths) as "Month(s)",
ADD_MONTHS(Date2, - TRUNC(NumOfMonths)) - Date1 as "Day(s)"
from MONTHS_BTW;
SQLFiddle演示:
+--------------+--------------+-----------------+-----------+--------+
| DATE_1 | DATE_2 | NUM_OF_MONTHS | MONTH(S) | DAY(S) |
+--------------+--------------+-----------------+-----------+--------+
| JAN 01 2012 | MAR 25 2012 | 2.774193548387 | 2 | 24 |
| JAN 01 2012 | JAN 01 2013 | 12 | 12 | 0 |
| JAN 01 2012 | JAN 01 2012 | 0 | 0 | 0 |
| FEB 28 2013 | MAR 01 2013 | 0.129032258065 | 0 | 1 |
| FEB 28 2013 | APR 01 2013 | 1.129032258065 | 1 | 1 |
+--------------+--------------+-----------------+-----------+--------+
注意,对于最后两个日期,Oracle 报告了月份的小数部分(即天数)错误。
0.1290
对应于 Oracle 认为一个月有
31
天的情况下确切的
4
天(对于三月和四月都是如此)。