MicroStrategy Weighty

MONTHS BETWEEN Function
In Oracle/PLSQL, the months_between function returns the number of months between date1 and date2.

The syntax for the months_between function is:

months_between( date1, date2 )

date1 and date2 are the dates used to calculate the number of months.

If a fractional month is calculated, the months_between function calculates the fraction based on a 31-day month.

Applies To:

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

Example #1:

months_between (to_date ('2003/01/01', 'yyyy/mm/dd'), to_date ('2003/03/14', 'yyyy/mm/dd') )

would return -2.41935483870968

Example #2

months_between (to_date ('2003/07/01', 'yyyy/mm/dd'), to_date ('2003/03/14', 'yyyy/mm/dd') )

would return 3.58064516129032

Example #3

months_between (to_date ('2003/07/02', 'yyyy/mm/dd'), to_date ('2003/07/02', 'yyyy/mm/dd') )

would return 0

Example #4

months_between (to_date ('2003/08/02', 'yyyy/mm/dd'), to_date ('2003/06/02', 'yyyy/mm/dd') )

would return 2

If you need the number of months, you need to use this function.
The syntax for the function is:
MONTHS_BETWEEN(date1, date2)
SQL> SELECT MONTHS_BETWEEN('01-JAN-01','01AUG01') AS MONTHS_BETWEEN FROM Dual
MONTHS_BETWEEN
--------------
-7

Experimenting with MONTHS_BETWEEN.

SQL> SELECT MONTHS_BETWEEN('25-DEC-97','02-JUN-97') "Fractional", MONTHS_BETWEEN('02-FEB-97','02-JUN-97') "Integer" from DUAL;

Fractional Integer
---------- ----------
6.74193548 -4

SQL> SELECT MONTHS_BETWEEN('25-MAY-2005', '15-JAN-2005') FROM dual;

MONTHS_BETWEEN('25-MAY-2005','15-JAN-2005')
-------------------------------------------
4.32258065

SELECT MONTHS_BETWEEN(TO_DATE('22SEP2006','ddMONyyyy'),
  2    TO_DATE('13OCT2001','ddMONyyyy')) "Months difference"
  3  FROM dual;

Months difference
-----------------
       59.2903226

CREATE TABLE product (
  2       product_name     VARCHAR2(25),
  3       product_price    NUMBER(4,2),
  4       quantity_on_hand NUMBER(5,0),
  5       last_stock_date  DATE);

Table created.

SQL> INSERT INTO product VALUES ('Small Widget', 99, 1, '15-JAN-03');

1 row created.

SQL> INSERT INTO product VALUES ('Medium Widget', 75, 1000, '15-JAN-02');

1 row created.

SQL> INSERT INTO product VALUES ('Product Number', 50, 100, '15-JAN-03');

1 row created.

SQL> INSERT INTO product VALUES ('Round Church Station', 25, 10000, null);

1 row created.

SQL> SELECT product_name,
  2         last_stock_date,
  3         MONTHS_BETWEEN(SYSDATE, last_stock_date) STOCK_MONTHS
  4  FROM   product;

PRODUCT_NAME              LAST_STOC STOCK_MONTHS
------------------------- --------- ------------
Small Widget              15-JAN-03   65.1540293
Medium Widget             15-JAN-02   77.1540293
Product Number            15-JAN-03   65.1540293
Round Church Station

SQL> select months_between( sysdate, date'1971-05-18' )
2 from dual;
MONTHS_BETWEEN(SYSDATE,DATE'1971-05-18')
----------------------------------------
444.958945

SQL> SELECT MONTHS_BETWEEN(TO_DATE(TO_CHAR(TO_DATE('&date1'),
  2         'mmyyyy'), 'mmyyyy'),
  3         TO_DATE(TO_CHAR(TO_DATE('&date2'), 'mmyyyy'),
  4         'mmyyyy')) Diff_Date
  5  FROM   DUAL;

Pages