logo标签列表

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

sqloracledate-arithmetic
15

我需要获取两个日期之间的差值，例如如果差值为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天。

没有人有答案吗？ - Stanley Mungai
2
难道不应该是2个月零24天吗？ - Noel
9个回答
22
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 |

测试链接: SQLFiddle

请问dt2和dt1代表什么？我以为它们是一些日期？ - Stanley Mungai
1
希望你不介意我在你的查询中添加了一些样本数据。其他答案产生的结果都不够准确，大多数甚至远远不够接近。尤其是2月28日左右的结果显示出了明显的差异。假设每个月有30或31天并不总是可行的。我不确定是否有官方算法来计算月份和天数，但这些结果对我来说看起来最好。 - Jon Heller
@Stanley 我加入了 jonearles 并添加了两个更多的例子，用于闰年和带有时间的日期，以及链接到网站，在那里任何人都可以在线测试解决方案。 - ThinkJet
你能否尝试以下日期组合，并查看答案是否合理？2013-01-28和2013-02-28，2013-01-29和2013-02-28，2013-01-30和2013-02-28，以及2013-01-31和2013-02-28。 - David Aldridge
终于做到了，这就是我一直在寻找的！！！谢谢。花了一年又一个月 :) - Stanley Mungai
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 天的情况下确切的 4 天（对于三月和四月都是如此）。
不是很准确。它应该返回24天。 - Noel
@Ramblin'Man 感谢你的提醒。显然，将31作为“一个月的天数”似乎解决了问题。我实际上认为30和31都有时会返回确切的天数，有时会少1天（取决于选择哪些月份），但是四舍五入解决了这个问题。如果你愿意，请再看一下。 - Ravi K Thapliyal
2
乘以30或31并不是一个好主意。对于某些日期，特别是涉及到二月份时，它会出现错误。 - Noel
@Ramblin'Man，我进行了更多的测试。现在我明白你的意思了。 - Ravi K Thapliyal
使用30.41667，即(365/12)。这是一年中一个月的平均天数。我发现当我在SQLite查询中输入(365/12)时，它变成了30（它以某种方式使我的查询向下取整，尽管我不知道如何），因此我必须插入30.41667才能开始准确计算，所以要注意，在Oracle中也可能出现这种情况，特别是因为您必须将MONTHS_BETWEEN向下取整（或使用上面看到的TRUNC）以将其相减以获得小数部分，从而乘以此。 - vapcguy
2
我认为你的问题没有定义得足够清楚，原因如下。
依赖于 months_between 的答案必须处理以下问题：该函数报告 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？
1
你提出了一些很好的观点。这是一个简单而常见的问题，没有完美的答案。无论使用哪种解决方案，都应该包括免责声明。我仍然会将赏金授予被接受的答案，因为这对我来说感觉正确。但这可能只是因为我使用了 months_between 太多次了。 - Jon Heller
是的，正如Jon Heller所说，2-28到3-31之间的天数为“1”，而不是2-28到3-28 =“1”。这就是为什么当你计算天数时，你应该真正地计算天数，而不是使用“MONTHS_BETWEEN”的原因。 - vapcguy
1
这是您所指的吗？
select trunc(months_between(To_date('20120325', 'YYYYMMDD'),to_date('20120101','YYYYMMDD'))) months,
             round(To_date('20120325', 'YYYYMMDD')-add_months(to_date('20120101','YYYYMMDD'),
                           trunc(months_between(To_date('20120325', 'YYYYMMDD'),to_date('20120101','YYYYMMDD'))))) days
        from dual;
这也是正确的 :P - Stanley Mungai
1

在这里，我只是比较今天和表中的CREATED_DATEDATE字段之间的差异，显然这是过去的一个日期：

SELECT  
((FLOOR(ABS(MONTHS_BETWEEN(CREATED_DATE, SYSDATE))) / 12) * 12) || ' months, '  AS MONTHS,
-- we take total days - years(as days) - months(as days) to get remaining days
FLOOR((SYSDATE - CREATED_DATE) -      -- total days
(FLOOR((SYSDATE - CREATED_DATE)/365)*12)*(365/12) -      -- years, as days
-- this is total months - years (as months), to get number of months, 
-- then multiplied by 30.416667 to get months as days (and remove it from total days)
FLOOR(((SYSDATE - CREATED_DATE)/365)*12 - (FLOOR((SYSDATE - CREATED_DATE)/365)*12)) * (365/12))
|| ' days ' AS DAYS 
FROM MyTable

我使用(365/12)，即30.416667作为转换因子，因为我正在使用总天数并将年份和月份（以天数表示）移除，以获得余下的天数。无论如何，对我的目的来说这已经足够好了。

0

我发布的解决方案将考虑一个30天的月份

  select CONCAT (CONCAT (num_months,' MONTHS '), CONCAT ((days-(num_months)*30),' DAYS '))
  from ( 
  SELECT floor(Months_between(To_date('20120325', 'YYYYMMDD'),
   To_date('20120101', 'YYYYMMDD')))
   num_months,
   ( To_date('20120325', 'YYYYMMDD') - To_date('20120101', 'YYYYMMDD') )
   days
  FROM   dual);
3
乘以30或31并不是一个好主意，对于某些日期特别是涉及到2月的时候会出问题。 - Noel
我乘以(365/12)或30.41667，但使用它的方法不是直接使用该结果数字找到实际天数，就像上面那样。您可以使用它将MONTHS_BETWEEN转换为天数，从而得到总天数，然后减去以天为单位的时间量（以月为单位），以找到实际剩余天数。 - vapcguy
-2
在Oracle SQL中找出两个日期之间的年份-月份-日期。 
select 
trunc(trunc(months_between(To_date('20120101', 'YYYYMMDD'),to_date('19910228','YYYYMMDD')))/12) years ,
trunc(months_between(To_date('20120101', 'YYYYMMDD'),to_date('19910228','YYYYMMDD'))) 
-
(trunc(trunc(months_between(To_date('20120101', 'YYYYMMDD'),to_date('19910228','YYYYMMDD')))/12))*12
months,
             round(To_date('20120101', 'YYYYMMDD')-add_months(to_date('19910228','YYYYMMDD'),
                           trunc(months_between(To_date('20120101', 'YYYYMMDD'),to_date('19910228','YYYYMMDD'))))) days
        from dual;
-3
SELECT   (MONTHS_BETWEEN(date2,date1) +  (datediff(day,date2,date1))/30) as num_months,
datediff(day,date2,date1) as diff_in_days  FROM  dual;

// You should replace date2 with TO_DATE('2012/03/25', 'YYYY/MM/DD')
// You should replace date1 with TO_DATE('2012/01/01', 'YYYY/MM/DD')
// To get you results
DateDiff在哪个Oracle表中？因为在dual>中返回无效标识符。 - Stanley Mungai
是的，你说得对。我很快会为你提供针对Oracle的特定解决方案。 - Sami
如果datediff对您不起作用，只需尝试date2-date1而不是datediff(day,date2,date1)。无论哪种方法有效，请与我分享。谢谢。 - Sami
实际上，目前我没有Oracle引擎，否则我认为简单的SQL（语言）函数在Oracle上也可以工作。请参考：http://www.w3schools.com/sql/func_datediff_mysql.asp - Sami
请理解所需的输出，如果是122天，则应为2个月零14天。 - Stanley Mungai
DateDiff 是 MySQL 和 MS SQL 中的函数，而不是 Oracle。 - vapcguy
-4
请看下面的查询（假设 @dt1 >= @dt2）;
Declare @dt1 datetime = '2013-7-3'
Declare @dt2 datetime = '2013-5-2'

select abs(DATEDIFF(DD, @dt2, @dt1)) Days,
case when @dt1 >= @dt2
    then case when DAY(@dt2)<=DAY(@dt1)
        then Convert(varchar, DATEDIFF(MONTH, @dt2, @dt1)) + CONVERT(varchar, ' Month(s) ') + Convert(varchar, DAY(@dt1)-DAY(@dt2)) + CONVERT(varchar, 'Day(s).')
        else Convert(varchar, DATEDIFF(MONTH, @dt2, @dt1)-1) + CONVERT(varchar, ' Month(s) ') + convert(varchar, abs(DATEDIFF(DD, @dt1, DateAdd(Month, -1, @dt1))) - (DAY(@dt2)-DAY(@dt1))) + CONVERT(varchar, 'Day(s).')
    end
    else 'See asumption: @dt1 must be >= @dt2'
end In_Months_Days

返回：

Days | In_Months_Days

62   |   2 Month(s) 1Day(s).
1
Oracle问题，不是SQL Server问题。 - David Aldridge
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接