在sql中的月份和日期之间获取两个日期之间的差异

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 |

链接到测试：SQLFiddle

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!