MONTHS_BETWEEN giving strange output. What is the logic behind it?
852282Apr 1 2011 — edited Apr 1 2011Hi,
Why the third and fourth queries show values less than 1?
SELECT MONTHS_BETWEEN(TO_DATE('28-FEB-2011'),
TO_DATE('01-FEB-2011')) FROM DUAL; -- 0.870967741935484
SELECT MONTHS_BETWEEN(TO_DATE('28-FEB-2011'),
TO_DATE('31-JAN-2011')) FROM DUAL; -- 1
SELECT MONTHS_BETWEEN(TO_DATE('28-FEB-2011'),
TO_DATE('30-JAN-2011')) FROM DUAL; -- 0.935483870967742 ??
SELECT MONTHS_BETWEEN(TO_DATE('28-FEB-2011'),
TO_DATE('29-JAN-2011')) FROM DUAL; -- 0.967741935483871 ??
SELECT MONTHS_BETWEEN(TO_DATE('28-FEB-2011'),
TO_DATE('28-JAN-2011')) FROM DUAL; -- 1
My understanding is that for the first and second query, oracle considers 28 days February month and for the rest of the queries, oracle considers 31 days January month. But even then, when looking at the outputs, it doesn't look great!
If the user questions me, I will have to at least give some stupid answer to escape!!!!
Is there any other logic behind it?
Sebastian Thomas