Term: MONTHS_BETWEEN

Definition:
The Oracle MONTHS_BETWEEN calculates the month difference between two given dates. It accepts two date inputs and returns an integer value. It also considers the difference of time components of the two dates. When both dates have the same day component or are the last day of the month, the returned value is a whole number. If not, the returned value includes a fraction that approximates the difference in the days based on a 31-day month.

Example Syntax:

MONTHS_BETWEEN (D1, D2)

where D1 and D2 are of date datatype.

Example Usage:

As shown in the SQL statement below, if the first argument date is greater than second argument date, the result is positive.

SQL> SELECT MONTHS_BETWEEN(TO_DATE('10-12-2010','DD-MM-YYYY HH24:MI:SS'), 

     			            TO_DATE('23-09-2010','DD-MM-YYYY HH24:MI:SS')

                                                               ) DIFF

  FROM DUAL

  /



  DIFF

----------

2.58064516

If the first argument date is less than the second argument date, the result is negative.

SQL> SELECT MONTHS_BETWEEN(SYSDATE, SYSDATE+1 ) DIFF

  FROM DUAL

  /



  DIFF

----------

-.03225806

Related Links:

Oracle Date Functions: MONTHS_BETWEEN

Related Code Snippets:

Months_Between function - The months_between function returns the number of months between date1 a...
Date Functions: MONTHS_BETWEEN - Returns the months in between (separating) two given dates.