在SQL中获取两个日期之间的差异(以月为单位)和以天为单位
问题描述:
我需要得到两个日期之间的差额,如果差异是84天,我应该输出2个月和14天,而我得到的代码就是总数.这是代码
I need to get the difference between two dates say if the difference is 84 days, I should probably have output as 2 months and 14 days, the code I have just gives the totals. Here is the code
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天.
I need for example the output for this query to be either 2 months and 14 days at worst, otherwise I won't mind if I can have the exact days after the months figure because those days are not really 14 because all months do not have 30 days.
答
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