Oracle SQL - Extract Year's Month's Day's from two dates.

 In this Post I explain to find Years, Months and days from Two dates.

1. Extract Total years

  SELECT TRUNC(MONTHS_BETWEEN(SYSDATE, '01-JAN-2018')/12) total_years
    FROM DUAL;

  OUTPUT

  ------

  2

  

2. Extract Total months

  SELECT (TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, '01-JAN-2018'), 12))) total_months
    FROM DUAL;

  

  OUTPUT

  ------

  10

3. Extract Total days

  SELECT (TRUNC(SYSDATE) - ADD_MONTHS('01-JAN-2018', 

                           TRUNC(MONTHS_BETWEEN(SYSDATE, '01-JAN-2018')))) total_days
    FROM DUAL;

  OUTPUT

  ------

  2

4. Concat Year Month Days

  SELECT TRUNC(MONTHS_BETWEEN(SYSDATE, '01-JAN-2018')/12) ||' Years '||                       (TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, '01-JAN-2018'), 12)))||' Months '||               (TRUNC(SYSDATE) - ADD_MONTHS('01-JAN-2018',                                           TRUNC(MONTHS_BETWEEN(SYSDATE, '01-JAN-2018'))))||' Days' yr_mm_dd

    FROM DUAL;

  OUTPUT

  ------

  2 Years 10 Months 2 Days

Thanks & Regards

R. Kalaiyarasan

+91 9944809554