日期函数months_between的用法

 

 

    MONTHS_BETWEEN (date1, date2)
    用于计算date1和date2之间有几个月。    如果date1在日历中比date2晚，那么MONTHS_BETWEEN()就返回一个正数。
    如果date1在日历中比date2早，那么MONTHS_BETWEEN()就返回一个负数。
    如果date1和date2日期一样，那么MONTHS_BETWEEN()就返回一个0。

       实验如下：

SQL> select months_between(to_date('2014-3-21','yyyy-mm-dd'), to_date('2014-1-10','yyyy-mm-dd')) months
  2  from dual;

    MONTHS
----------
2.35483871

SQL> select months_between(to_date('2014-1-10','yyyy-mm-dd'), to_date('2014-3-21','yyyy-mm-dd')) months
  2  from dual;

    MONTHS
----------
-2.3548387

SQL> select months_between(to_date('2014-1-10','yyyy-mm-dd'), to_date('2014-1-10','yyyy-mm-dd')) months
  2  from dual;

    MONTHS
----------
         0

--2014.3.21和2014.1.10之间，相差2个月加11天，11天按月换算成小数(在oracle里面，以31天为基数):
SQL> select 11/31 from dual;

     11/31
----------
 .35483871

      详细参见11.2联机文档:
      http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions102.htm#SQLRF00669

        MONTHS_BETWEEN returns number of months between dates date1 and date2. The month and the last day of the month are defined by the parameter NLS_CALENDAR. If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. 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.Otherwise Oracle Database calculates the fractional portion of the result based on a31-day month and considers the difference in time components date1 and date2.