Skip to Main Content
  • Questions
  • precision of months_between function

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Petr.

Asked: February 05, 2016 - 8:03 am UTC

Last updated: February 10, 2016 - 9:07 am UTC

Version: 11gR2

Viewed 1000+ times

You Asked

Hello!


SQL> select months_between(sysdate, sysdate - 0.669) dd from dual
2 union all
3 select months_between(sysdate, sysdate - 0.5) dd from dual;

DD
----------
,021580795
0

Why is that?
Herewith..

SQL> select months_between(to_date(sysdate,'DD.MM.YYYY HH24:MI:SS'), to_date(sysdate,'DD.MM.YYYY HH24:MI:SS') - 0.669) dd from dual
2 union all
3 select months_between(to_date(sysdate,'DD.MM.YYYY HH24:MI:SS'), to_date(sysdate,'DD.MM.YYYY HH24:MI:SS') - 0.5) dd from dual;

DD
----------
,021580795
,016129032




























































and Chris said...

Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2.


http://docs.oracle.com/database/121/SQLRF/functions114.htm#SQLRF00669

I'm not able to reproduce your example where subtracting half a day returns zero:

SQL> select months_between ( sysdate, sysdate - 0.669 ) dd from dual
  2  union all
  3  select months_between ( sysdate, sysdate - 0.5 ) dd from dual;

        DD
----------
.021580795
.016129032


I suggest you contact support about this.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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
Chris Saxon
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
Chris Saxon
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
Chris Saxon
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.