MONTHS_BETWEEN() function returns the number of months between two dates.
The following illustrates the syntax of the Oracle
MONTHS_BETWEEN(minuend_date, subtrahend_date );
MONTHS_BETWEEN() function requires two arguments, each of which can be a
DATE or expression evaluates to a
minuend_date is a date which is subtracted from.
subtrahend is also a date which is to be subtracted.
MONTHS_BETWEEN() function returns the number of months between two dates which is:
- a positive integer if
minuend_dateis later than
- a negative integer if
minuend_dateis earlier than
- an integer if
subtrahend_dateare either the same days or they are both the last days of the months.
In other cases, the
MONTHS_BETWEEN() function will calculate the fractional portion of the result based on the 31-day month and also consider the difference in time parts of
Let’s take some example to get a better understanding of how the
MONTHS_BETWEEN() function works.
A) Get the difference in months of dates with the same day
The following example returns a difference in months between two dates:
July 01 2017 and
January 01 2017:
SELECT MONTHS_BETWEEN( DATE '2017-07-01', DATE '2017-01-01' ) MONTH_DIFF FROM DUAL;
The result is 6 months as shown below:
B) Both date arguments are the last days of the months
The following statement returns 1 month because both arguments are the last day of the months:
SELECT MONTHS_BETWEEN( DATE '2017-03-31', DATE '2017-02-28' ) MONTH_DIFF FROM DUAL;
C) Both date arguments are not the same day
The following example returns a decimal because the days of the date arguments are not the same.
SELECT MONTHS_BETWEEN( DATE '2017-07-31', DATE '2017-08-15' ) MONTH_DIFF FROM DUAL;
It returns about -0.5 month:
In this tutorial, you have learned how to use the Oracle
MONTHS_BETWEEN() function to get the number of months between two dates.