如何在一列中获得两个日期之间的确切年，月，日期

Question

如何在一列中获取两个日期之间的确切年，月，日期oracle sql

下面是我的查询，目前我的输出显示为0年11个月30天，但我需要输出像1年0个月0天

select trunc(months_between(lease.lease_end_date,(lease.lease_start_date)) / 12) || ' Years ' || trunc(months_between(lease.lease_end_date,(trunc(lease.lease_start_date,'YY'))) - (trunc(months_between(lease.lease_end_date,(trunc(lease.lease_start_date,'YY'))) / 12) * 12)) || ' Months' ||' '|| ( trunc(lease.lease_end_date)- add_months((trunc(lease.lease_start_date,'YY')), trunc(months_between(lease.lease_end_date,(trunc(lease.lease_start_date,'YY')))))) || ' Days '
from dual 

这是我的查询，但我的输出显示为0年11个月30天，但我想要1年0个月0天

Answer 1

您可以使用:

SELECT lease_start_date,
lease_end_date,
EXTRACT(YEAR FROM year_months) || ' years '
|| EXTRACT(MONTH FROM year_months) || ' months '
|| EXTRACT(DAY FROM day_time) || ' days '
|| EXTRACT(HOUR FROM day_time) || ' hours '
|| EXTRACT(MINUTE FROM day_time) || ' minutes '
|| EXTRACT(SECOND FROM day_time) || ' seconds' AS difference
FROM   (
SELECT lease_start_date,
lease_end_date,
(lease_end_date - lease_start_date) YEAR TO MONTH AS year_months,
(lease_end_date - ADD_MONTHS(lease_start_date, TRUNC(MONTHS_BETWEEN(lease_end_date, lease_start_date)))) DAY TO SECOND AS day_time
FROM   lease
)

对于样本数据:

CREATE TABLE lease (lease_start_date, lease_end_date) AS
SELECT DATE '2020-02-29', DATE '2021-03-01' FROM DUAL UNION ALL
SELECT DATE '2020-02-29', DATE '2021-02-28' FROM DUAL UNION ALL
SELECT DATE '2020-02-28', DATE '2021-02-28' FROM DUAL UNION ALL
SELECT DATE '2020-02-01', DATE '2020-02-01' + INTERVAL '12:34:56' HOUR TO SECOND FROM DUAL

输出:

LEASE_START_DATE	LEASE_END_DATE	DIFFERENCE
2020-02-29 00:00:00	2021-03-01 00:00:00	1年0个月1天0小时0分0秒
2020-02-29 00:00:00	2021-02-28 00:00:00	1年0个月0天0小时0分0秒
2019-02-28 00:00:00	2019-02-28 00:00:00	1年0个月0天0小时0分0秒
2020-02-01 00:00:00	2020-02-01 12:34:56	0年0月0天12小时34分56秒