Oracle Applications: Query to get the Number of Years, Months and Days between two Given Dates

Saturday, April 5, 2014

SELECT

TRUNC(MONTHS_BETWEEN (LEAST(NVL(:P_TO_DATE,SYSDATE),SYSDATE), NVL(:START_DATE,:START_DATE))/12) Y

,TRUNC(MONTHS_BETWEEN (LEAST(NVL(:P_TO_DATE,SYSDATE),SYSDATE), NVL(:START_DATE,:START_DATE))) -

TRUNC(MONTHS_BETWEEN (LEAST(NVL(:P_TO_DATE,SYSDATE),SYSDATE), NVL(:START_DATE,:START_DATE))/12)*12 M

,TRUNC((MONTHS_BETWEEN (LEAST(NVL(:P_TO_DATE,SYSDATE),SYSDATE), NVL(:START_DATE,:START_DATE)) -

TRUNC(MONTHS_BETWEEN (LEAST(NVL(:P_TO_DATE,SYSDATE),SYSDATE), NVL(:START_DATE,:START_DATE)))) * 30 ) D

from dual

Pages