This content has been marked as final. Show 4 replies
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.
AbSHeik wrote:Hi Abhishek,
There two ways of finding months between two dates
ROUND((Date1 – Date2) / 365.25 * 12,2)
There is a slight difference between output from these two.
I think the result from the second statement should be more accurate, confirm?
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.
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.
select months_between(to_date('2012/02/29','yyyy/mm/dd'),to_date('2012/01/31','yyyy/mm/dd')) as DIFF from dual;