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

months between two date

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
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
confirm?
Confirmed.
• 3. Re: months between two date
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
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