Oracle MONTHS_BETWEEN Function: A Simple Guide

The MONTHS_BETWEEN function in Oracle SQL calculates the precise number of months between two dates.

This function is essential for any business logic that involves calculating tenure, age, or time differences in months. It's smart enough to return a whole number for "clean" intervals or a fractional number for "partial" months.

What is the MONTHS_BETWEEN Function in Oracle?

The MONTHS_BETWEEN(date1, date2) function returns the number of months between date1 and date2.

• If date1 (the end date) is later than date2 (the start date), the result is positive.
• If date1 is earlier than date2, the result is negative.

The function has two ways of returning a result:

1. Integer (Whole Number): It returns a whole number if the dates are "compatible." This happens when:
   • Both dates are the same day of the month (e.g., March 15th and May 15th).
   • Both dates are the last day of their respective months (e.g., Feb 28th and Apr 30th).
2. Fraction (Decimal Number): If the days of the month are different, Oracle calculates the fractional part based on a 31-day month.

MONTHS_BETWEEN Function Syntax

The syntax for MONTHS_BETWEEN is as follows:

MONTHS_BETWEEN(date1, date2)

Let's break that down:

• date1: This is your "end date" or the later date.
• date2: This is your "start date" or the earlier date.

To get a positive result, always put the later date first.

Oracle MONTHS_BETWEEN Function Examples

Here are two practical examples of how to use MONTHS_BETWEEN.

Example 1: Getting an Integer Result with MONTHS_BETWEEN

This example shows the two ways you can get a "clean" whole number as a result. We will check a pair of dates with the same day, and a pair of dates that are both the last day of their month.

Query:

SELECT 
  MONTHS_BETWEEN(
    DATE '2025-05-10', 
    DATE '2025-01-10'
  ) AS "Same_Day_Result",
  
  MONTHS_BETWEEN(
    DATE '2025-04-30', 
    DATE '2025-02-28'
  ) AS "Last_Day_Result"
FROM DUAL;

Result:

Same_Day_Result Last_Day_Result
--------------- ---------------
              4               2

In both cases, the function correctly identified the full-month intervals and returned a whole number.

Example 2: Getting a Fractional Result with MONTHS_BETWEEN

This example shows what happens when the days of the month do not align. Let's find the difference between Feb 2nd and Jan 1st.

Query:

SELECT 
  MONTHS_BETWEEN(
    TO_DATE('2025-02-02', 'YYYY-MM-DD'),
    TO_DATE('2025-01-01', 'YYYY-MM-DD')
  ) AS "Fractional_Months"
FROM DUAL;

Result:

Fractional_Months
-----------------
       1.03225806

Why this result? The function calculates it this way:

1. From Jan 01 to Feb 01 is exactly 1 full month.
2. The leftover time is the difference between Feb 02 and Feb 01, which is 1 day.
3. Oracle calculates the fraction by dividing the leftover days by 31: 1 / 31 = 0.03225806.
4. The total is 1 + 0.03225806 = 1.03225806.