我需要得到两个日期之间的差异说如果差额是84天,我应该可以输出2个月和14天,代码我刚刚给出总计.这是代码
SELECT Months_between(To_date('20120325','YYYYMMDD'),To_date('20120101','YYYYMMDD'))
num_months,( To_date('20120325','YYYYMMDD') - To_date('20120101','YYYYMMDD') )
diff_in_days
FROM dual;
输出为：
NUM_MONTHS DIFF_IN_DAYS 2.774193548 84
我需要这个查询的输出是最糟糕的2个月和14天,否则我不会介意,如果我可以有几个月后的确切的日子,因为那些日子真的不是14,因为所有月份都没有30天.
解决方法
select
dt1,dt2,trunc( months_between(dt2,dt1) ) mths,dt2 - add_months( dt1,trunc(months_between(dt2,dt1)) ) days
from
(
select date '2012-01-01' dt1,date '2012-03-25' dt2 from dual union all
select date '2012-01-01' dt1,date '2013-01-01' dt2 from dual union all
select date '2012-01-01' dt1,date '2012-01-01' dt2 from dual union all
select date '2012-02-28' dt1,date '2012-03-01' dt2 from dual union all
select date '2013-02-28' dt1,date '2013-03-01' dt2 from dual union all
select date '2013-02-28' dt1,date '2013-04-01' dt2 from dual union all
select trunc(sysdate-1) dt1,sysdate from dual
) sample_data
结果：
| DT1 | DT2 | MTHS | DAYS | ---------------------------------------------------------------------------- | January,01 2012 00:00:00 | March,25 2012 00:00:00 | 2 | 24 | | January,01 2012 00:00:00 | January,01 2013 00:00:00 | 12 | 0 | | January,01 2012 00:00:00 | 0 | 0 | | February,28 2012 00:00:00 | March,01 2012 00:00:00 | 0 | 2 | | February,28 2013 00:00:00 | March,01 2013 00:00:00 | 0 | 1 | | February,28 2013 00:00:00 | April,01 2013 00:00:00 | 1 | 1 | | August,14 2013 00:00:00 | August,15 2013 05:47:26 | 0 | 1.241273 |
