Petr Semenov, February 05, 2016 - 9:34 am UTC
Thanks for the answer!
And I have this on diffirent test-database.
SQL> select months_between(to_date('01.01.2016 23:59:59', 'DD.MM.YYYY HH24:MI:SS
'),
2 to_date('01.01.2016 00:00:00', 'DD.MM.YYYY HH24:MI:S
S')) dd
3 from dual
4 union all
5 select months_between(to_date('02.01.2016 00:00:00', 'DD.MM.YYYY HH24:MI:SS
'),
6 to_date('01.01.2016 00:00:00', 'DD.MM.YYYY HH24:MI:S
S')) dd
7 from dual;
DD
----------
0
,032258065
February 05, 2016 - 9:56 am UTC
I should have included the complete quote:
If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer
So this is expected functionality. If the dates are the same, then the difference in times is always going to be less than 1/31. So this will round to zero.
I guess in your original example you ran it at a time when sysdate - 0.5 and sysdate - 669 returned different days.
Some strange behavior of the function
Vladimir, February 05, 2016 - 10:28 am UTC
We will receive an incorrect result when the periods in one day.
SQL> with qd as (
2 select 1 as a, MONTHS_BETWEEN(to_date('02.01.2000 00:00:00','DD.MM.YYYY HH2
4:MI:SS'),to_date('01.01.2000 23:59:59','DD.MM.YYYY HH24:MI:SS')) as b from dual
3 union all
4 select 1, MONTHS_BETWEEN(to_date('01.01.2000 23:59:59','DD.MM.YYYY HH24:MI:
SS'),to_date('01.01.2000 00:00:00','DD.MM.YYYY HH24:MI:SS')) from dual
5 )
6 SELECT q.a, SUM (q.b)
7 FROM qd q
8 GROUP BY q.a
9 ;
A SUM(Q.B)
---------- ----------
1 3,7336E-07
February 05, 2016 - 4:11 pm UTC
The output of that query looks fine to me. There are two different dates in the first part of the union. So Oracle calculates 1/31/86400 (seconds in a day).
The both dates in the second query are on the same day. So this returns zero - expected behaviour!
Giving an unaggregated output of:
A B
---------- ----------
1 3.7E-07
1 0
Sum these up and you get the same value you have.
Vladimir, February 10, 2016 - 8:06 am UTC
but then
months_between (a) + months_between (b) != months_between(a+b),
where
a = 01.01.2016 23:59:59, 01.01.2016 00:00:00
b = 02.01.2016 23:59:59, 02.01.2016 00:00:00
a+b = 02.01.2016 23:59:59, 01.01.2016 00:00:00
February 10, 2016 - 9:07 am UTC
Yes, the output of those are different. That is as expected according to the documentation.
If you think this should change then raise an enhancement request.