Oracle PL/SQL - MONTHS_BETWEEN function

MONTHS_BETWEEN Function : 

Syntax : MONTHS_BETWEEN ('Date1','Date2');

  • In Oracle PL/SQL MONTHS_BETWEEN function returns the number of months between two dates.
  • Input argument to the MONTHS_BETWEEN function should be of data type 'date'.
  • Return Type of MONTHS_BETWEEN is a number.
  • If Date 1 is greater than Date 2 then the return number is positive
  • If Date 1 is less than Date 2 then the return number is negative
  • If Date 1 and Date 2  have the same day component or are the last day of the month, the return number is a whole number,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.

EXAMPLE 1 : ( Date 1 is greater than Date 2)

SELECT MONTHS_BETWEEN (TO_DATE('02-02-2015','MM-DD-YYYY'),
       TO_DATE('01-01-2015','MM-DD-YYYY') ) "Months"
    FROM DUAL;

    Months
----------
1.03225806
EXAMPLE 2 : ( Date 1 is less than Date 2)

SELECT MONTHS_BETWEEN (SYSDATE,SYSDATE+1) "Months"
    FROM DUAL;

Months
----------
-.03225806
EXAMPLE 3 : ( Date 1 and Date 2 has same day)

SELECT MONTHS_BETWEEN (TO_DATE ('2015-10-25', 'YYYY-MM-DD'), TO_DATE ('2015-03-25', 'YYYY-MM-DD')) "Months"
       FROM DUAL;

Months
----------
7
Labels: , , , ,