4 Replies Latest reply: Nov 17, 2012 7:37 AM by ranit B RSS

    months between two date

    9876564
      There two ways of finding months between two dates
       ROUND((Date1 – Date2) / 365.25 * 12,2) 
      and
       ROUND(months_between(date1,date2),2) 
      There is a slight difference between output from these two.

      I think the result from the second statement should be more accurate, confirm?

      Abhishek
        • 1. Re: months between two date
          Hoek
          I think the result from the second statement should be more accurate, confirm?
          Yes, you should use MONTHS_BETWEEN and round as desired.
          Dividing by 365.25 will produce wrong results sooner or later, especially when you run into leap years.
          • 2. Re: months between two date
            SomeoneElse
            confirm?
            Confirmed.
            • 3. Re: months between two date
              ranit B
              AbSHeik wrote:
              There two ways of finding months between two dates
               ROUND((Date1 – Date2) / 365.25 * 12,2) 
              and
               ROUND(months_between(date1,date2),2) 
              There is a slight difference between output from these two.

              I think the result from the second statement should be more accurate, confirm?

              Abhishek
              Hi Abhishek,

              I also agree with this.
              Coz 365.25 = (365 + (1/4))

              This 0.25 actually the extra year of a Leap Year, distributed equally among 4 years. But, when the 1st formula is evaluated, the calculation might cause difference...
              So as far as Oracle is concerned, MONTHS_BETWEEN should be used.

              Please rectify me if i'm wrong.

              Ranit B.
              • 4. Re: months between two date
                972756
                Also, when comparing the last day of two months, months_bewteen will give you an integer as result, regardless the number of days in the months.

                e.g.
                select months_between(to_date('2012/02/29','yyyy/mm/dd'),to_date('2012/01/31','yyyy/mm/dd')) as DIFF from dual;

                DIFF
                ------------
                1