Hello,,,
While I'm trying a full test on how the MONTHS_BETWEEN built-in function in Oracle works (I'm using Oracle DB 11g), I noticed that this function doesn't take care about the number of days regarding whether the month is 30 days, 31 days, 28 days or 29 days.
If the specified days in the parameters of this function are the first of the months, then this function returns exactly the number of months.
But, if those parameters are specified other than the first of months, then the fraction returned is not correct. The following is a scenario about what I'm talking about:
Scenario 1_
SQL> SELECT MONTHS_BETWEEN('01-FEB-2010', '01-JAN-2010') A, 31/31 B
2 FROM DUAL;
A B
---------- ----------
1 1
In the following scenario, we can see that a portion of day is 1/31 which is correct because January is 31 days and there is only 1 day between the 2nd and the first of it:
Scenario 2_
SQL> SELECT MONTHS_BETWEEN('02-JAN-2010', '01-JAN-2010') A, 1/31 B
2 FROM DUAL;
A B
---------- ----------
.032258065 .032258065
But in the following scenario, the funtion still dividing by 31 days even though February is 28 days for 2010:
Scenario 3_
SQL> SELECT MONTHS_BETWEEN('02-FEB-2010', '01-FEB-2010') A, 1/31 B, 1/28 C
2 FROM DUAL;
A B C
---------- ---------- ----------
.032258065 .032258065 .035714286
Look to A and B => they are identical, which if I'm not wrong, they shouldn't be !! In my opinion the value C should be the right one when taking into consideration the calculation of fraction of days.
Let me know please your points of view regarding this function.
Thanks,
Joe