Hello
I (colleague) was using months_between function and noticed some "glitch"(?) when the starting date is 28 feb.
As shown bellow
Difference between 28-02 and 31-07 should be 5 months, because we are comparing last day of each month. Difference between 28-02 and 28-07 should(?) be 5 because it takes a month between the two dates. But difference between 28-02 and both 30-07 and 29-07 is MORE than a month, which makes no sense looking at fact that it didn't pass a whole month between the dates (should be 5, no?).
Does anyone know how this function works? I couldn't find satisfactory explanation for the behavior regarding the "february use case".
By the way, ADD_MONTHS does was is supposed(?) to do; adding 5 months to 28-02-2017 returns 31-07
SELECT
select MONTHS_BETWEEN(to_date('31072017','ddmmyyyy'), to_date('28022017','ddmmyyyy')) diff from dual union all
select MONTHS_BETWEEN(to_date('30072017','ddmmyyyy'), to_date('28022017','ddmmyyyy')) from dual union all
select MONTHS_BETWEEN(to_date('29072017','ddmmyyyy'), to_date('28022017','ddmmyyyy')) from dual union all
select MONTHS_BETWEEN(to_date('28072017','ddmmyyyy'), to_date('28022017','ddmmyyyy')) diff from dual
;
RESULT
5
5.06451612903225806451612903225806451613
5.03225806451612903225806451612903225806
5