11 Replies Latest reply: Oct 4, 2013 9:46 AM by JonWat RSS

    Query to return Years, Months and Days

    Adit102

      Hi,

       

      I am trying to return years, months and days between two dates.

       

      Years - 12, Months - 0, Days - 0

       

      below is the query,

       

      select LEASE_TERMINATION_DATE , LEASE_COMMENCEMENT_DATE  ,

      Extract(YEAR FROM (LEASE_TERMINATION_DATE -  LEASE_COMMENCEMENT_DATE  ) year to month) Years

      ,Extract(MONTH FROM (LEASE_TERMINATION_DATE - LEASE_COMMENCEMENT_DATE  ) year to month) Months

      ,Extract(day FROM (LEASE_TERMINATION_DATE - LEASE_COMMENCEMENT_DATE) day to second) Days

      from pn_lease_details_all;

       

      Here, LEASE_TERMINATION_DATE is 31/10/2015 and LEASE_COMMENCEMENT_DATE is 01/11/2003.

       

      Returns Years - 12, Months - 0, Days- 4382

       

      This is returning Years and months correctly, but not days. As the query says 'day to second', it returns number of days.

      What changes do I need so that query will retun 0 days.

       

      Thanks,

      Aditya