How to get the exact year,month,date between two dates in one column oracle sql-CodePudding

How to get the exact year,month,date between two dates in one column oracle sql

Below is my query ,currently my output is showing as 0 Years 11 Months 30 Days but i need output like 1 Years 0 Months 0 Days

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

this is my query but my output is showing as 0 years 11 months 30 Days but i want 1 year 0 months 0 days

CodePudding user response：

You can use:

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
)

Which, for the sample data:

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

Outputs:

LEASE_START_DATE LEASE_END_DATE DIFFERENCE

2020-02-29 00:00:00 2021-03-01 00:00:00 1 years 0 months 1 days 0 hours 0 minutes 0 seconds

2020-02-29 00:00:00 2021-02-28 00:00:00 1 years 0 months 0 days 0 hours 0 minutes 0 seconds

2020-02-28 00:00:00 2021-02-28 00:00:00 1 years 0 months 0 days 0 hours 0 minutes 0 seconds

2020-02-01 00:00:00 2020-02-01 12:34:56 0 years 0 months 0 days 12 hours 34 minutes 56 seconds

LEASE_START_DATE	LEASE_END_DATE	DIFFERENCE
2020-02-29 00:00:00	2021-03-01 00:00:00	1 years 0 months 1 days 0 hours 0 minutes 0 seconds
2020-02-29 00:00:00	2021-02-28 00:00:00	1 years 0 months 0 days 0 hours 0 minutes 0 seconds
2020-02-28 00:00:00	2021-02-28 00:00:00	1 years 0 months 0 days 0 hours 0 minutes 0 seconds
2020-02-01 00:00:00	2020-02-01 12:34:56	0 years 0 months 0 days 12 hours 34 minutes 56 seconds

db<>fiddle here