CODEWITHSUNDEEP
sqloracle-database
Abinnaya
Abinnaya

Reputation: 223

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

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

Upvotes: 0

Views: 226

Answers (1)

MT0
MT0

Reputation: 167822

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

db<>fiddle here

Upvotes: 1

Related Questions