Oracle MONTHS_BETWEEN By Practical Examples

The Oracle MONTHS_BETWEEN() function returns the number of months between two dates.

Syntax

The following illustrates the syntax of the Oracle MONTHS_BETWEEN() function:

MONTHS_BETWEEN(minuend_date, subtrahend_date );Code language: SQL (Structured Query Language) (sql)

Arguments

The Oracle MONTHS_BETWEEN() function requires two arguments, each of which can be a DATE or expression evaluates to a DATE:

1) minuend_date

The minuend_date is a date that is subtracted from.

2) subtrahend_date

The subtrahend is also a date that is to be subtracted.

Return value

The MONTHS_BETWEEN() function returns the number of months between two dates which is:

a positive integer if minuend_date is later than subtrahend_date.
a negative integer if minuend_date is earlier than subtrahend_date.
an integer if minuend_date and subtrahend_date are either the same days or they are both the last days of the month.

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 minuend_date and subtrahend_date.

Examples

Let’s take some examples to get a better understanding of how the MONTHS_BETWEEN() function works.

A) Get the difference in months of dates on 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;
Code language: SQL (Structured Query Language) (sql)

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 on the last day of the month:

SELECT
  MONTHS_BETWEEN( DATE '2017-03-31', DATE '2017-02-28' ) MONTH_DIFF
FROM
  DUAL;
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

It returns about -0.5 month:

-0.4838709677419354838709677419354838709677 
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Oracle MONTHS_BETWEEN() function to get the number of months between two dates.

Was this tutorial helpful?