在sql中获取以月和日为单位的两个日期之间的差异

Question

我需要得到两个日期之间的差异，比如说，如果差异是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天。

Answer 1

我认为你的问题定义不够明确，原因如下。

依赖于两个月之间的回答必须处理以下问题：职能部门报告2013-02-28和2013-03-31之间以及2013-01-28和2013-02-28之间的一个月，在2013-01-31和2013-02-28之间（我怀疑一些回答者在实践中没有使用这些功能，或者现在必须检查一些生产代码！）

这是记录在案的行为，在这种行为中，既是各自月份的最后一个日期，又或者是在一个月的同一天，被判断为相隔整数个月。

因此，当将2013-02-28与2013-01-28或2013-01-31进行比较时，您会得到相同的“1”结果，但是将其与2013-01-29或2013-01-30进行比较会分别给出0.967741935484和0.935483870968-因此，当一个日期接近另一个日期时，此函数报告的差异会增加。

如果这不是一个可接受的情况，那么你将不得不编写一个更复杂的函数，或者仅仅依靠假设每月30天的计算。在后一种情况下，你将如何处理2013-02-28和2013-03-31？

Answer 2

为正确性而更新。最初的回答是@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天（3月和4月）。

Answer 3

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 | 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 |

链接到test:SQLFiddle