But...
Paul, March 09, 2006 - 12:39 pm UTC
SELECT months_between(to_date('30-03-2006', 'DD-MM-YYYY'),
to_date('28-02-2006', 'DD-MM-YYYY')) diff
FROM dual;
Returns
DIFF
----------------------
1.06451612903225806451612903225806451613
Meaning that there is more than a month between the two dates. Which means that months_between cannot be used as a diff between two dates.
Regards
Paul
March 09, 2006 - 3:41 pm UTC
point was? I believe we made that point?
un-understandable results of Oracle Date functions
Rehan, May 30, 2007 - 1:14 am UTC
I do Agree with Alex
Following are some un-understandable results of Oracle Date functions (Contradicted to Each other).
SQL> select add_months('30-Mar-07' ,-1) from dual;
ADD_MONTH
---------
28-FEB-07
SQL> select months_between('30-Mar-07', '28-Feb-07') from dual;
MONTHS_BETWEEN('30-MAR-07','28-FEB-07')
---------------------------------------
1.06451613 (WHY? It must be 1 according to first query)
SQL> select months_between('31-Mar-07', '28-Feb-07') from dual;
MONTHS_BETWEEN('31-MAR-07','28-FEB-07')
---------------------------------------
1 (WHY<1.06451613)
May 30, 2007 - 11:30 am UTC
no, months are strange things.
In real life.
If you go back one month from march 30th, the 2nd to last day of the month of March, what DAY do you (you - you personally) get??? I get "feb - 30, but wait, that doesn't exist, so it must be the 28th, unless of course it is a leap year...)
And when you are on the first day of a month and add a month, you get the first day of the next month - whether that is 28, 29, 30, or 31 days away right?
And the same with the last day of the month, if you are on the last day of a month and go back/forward a month - I get (in my head and in oracle) the last day of the prior/next month.
MONTHS are not consistent. It is like adding a YEAR - with leap years, you end up with different durations - sometimes 365, sometimes 366 days.
It is all consistently correct to and the way I was brought up on date arithmetic.
un-understandable results of Oracle Date functions
Rehan, May 30, 2007 - 1:14 am UTC
I do Agree with Alex
Following are some un-understandable results of Oracle Date functions (Contradicted to Each other).
SQL> select add_months('30-Mar-07' ,-1) from dual;
ADD_MONTH
---------
28-FEB-07
SQL> select months_between('30-Mar-07', '28-Feb-07') from dual;
MONTHS_BETWEEN('30-MAR-07','28-FEB-07')
---------------------------------------
1.06451613 (WHY? It must be 1 according to first query)
SQL> select months_between('31-Mar-07', '28-Feb-07') from dual;
MONTHS_BETWEEN('31-MAR-07','28-FEB-07')
---------------------------------------
1 (WHY<1.06451613)
un-understandable results of Oracle Date functions
Rehan, May 30, 2007 - 1:14 am UTC
I do Agree with Alex
Following are some un-understandable results of Oracle Date functions (Contradicted to Each other).
SQL> select add_months('30-Mar-07' ,-1) from dual;
ADD_MONTH
---------
28-FEB-07
SQL> select months_between('30-Mar-07', '28-Feb-07') from dual;
MONTHS_BETWEEN('30-MAR-07','28-FEB-07')
---------------------------------------
1.06451613 (WHY? It must be 1 according to first query)
SQL> select months_between('31-Mar-07', '28-Feb-07') from dual;
MONTHS_BETWEEN('31-MAR-07','28-FEB-07')
---------------------------------------
1 (WHY<1.06451613)
Leap Year
Yang, May 30, 2007 - 10:11 pm UTC
<quote>I get "feb - 30, but wait, that doesn't exist, so it must be the 28th, unless of course it is a leap year...</quote>
Come on Tom, even in leap you won't be able to get Feb-30. 29th is the "biggest" date you can get from Feb. ^_^
May 31, 2007 - 9:57 am UTC
right, but I said
"feb - 30, but wait, that doesn't exist, so it must be the 28th, unless of course it is a leap year..."
IN WHICH CASE IT IS THE 29th..... I didn't say it would be the 30th - the ... just says it would be "not the 28th" is all :)
MONTHS_BETWEEN
Babu, June 04, 2007 - 3:21 am UTC
Tom, I have one more clarification required from you. If it is the case, then months_between('28-Feb-07','30-Jan-07') should give 1 as the first value is last day of the month and the other value is from Prev month. So the value should be 1. But it is giving zero. Can we summarily say that Oracle Date function is using 30 as the month count?
June 05, 2007 - 7:57 am UTC
no it does NOT give zero:
ops$tkyte%ORA10GR2> select months_between(to_date('28-Feb-07','dd-mon-yy'),to_date('30-Jan-07','dd-mon-yy')) months_btwn,
2 last_day(to_date('28-feb-2007','dd-mon-yyyy')),
3 last_day(to_date('30-jan-2007','dd-mon-yyyy'))
4 from dual
5 /
MONTHS_BTWN LAST_DAY( LAST_DAY(
----------- --------- ---------
.935483871 28-FEB-07 31-JAN-07
and 30-jan is NOT the last day of the prior month.
MONTHS_BETWEEN
Babu, June 06, 2007 - 7:25 am UTC
Regretting the error.The formula that we are using is FLOOR(MONTHS_BETWEEN('28-Feb-07','30-Jan-07')). But the problem is it is giving value as 1 for FLOOR(MONTHS_BETWEEN('28-Feb-07','28-Jan-07')) and 0 for FLOOR(MONTHS_BETWEEN('28-Feb-07','30-Jan-07')) which seems not logical where the date in case I is lesser than Case II.
Though the explanation that I may get is, case I consists of same day of previous month where as case II has different days. But I believe there needs to be more logic applied and hope there is better logic applied for this calculation in future versions.
June 06, 2007 - 1:43 pm UTC
why doesn't it seem logical
the rules are documented, it follows the rules.
problem is "months" are NOT a fixed unit of time.
Calculation Logic
Gireesh Puthumana, September 30, 2014 - 1:03 pm UTC
Hi Tom,
How Oracle calculates this?
I tried below query (getting difference in days and dividing by 31 or 30):
SELECT MONTHS_BETWEEN (D1,D2) MONTH_DIFF_FROM_ORACLE_FN,
(D1-D2)/31 MONTH_DIFF_DIV31,
(D1-D2)/30 MONTH_DIFF_DIV30
FROM (SELECT TO_DATE('12-06-2014', 'DD-MM-YYYY') D1,
TO_DATE('01-04-2014', 'DD-MM-YYYY') D2
FROM DUAL);
Result:
MONTH_DIFF_FROM_ORACLE_FN: 2.35483870967742
MONTH_DIFF_DIV31: 2.32258064516129
MONTH_DIFF_DIV30: 2.4
How should I calculate it exactly the same way Oracle does manually? (say in another programming language)
Bump - Calculation Logic
Tres, October 25, 2017 - 1:06 pm UTC
Bump for Gireesh Puthumana.
where can we see the actual source code rather than fumbling around a few edge cases?
I need to implement this in R; I've found a single combination of dates that doesn't agree with the package I'm using between SQL and R. '10-25-2017' and '04-23-2014' differ at the second decimal place