MONTHS_BETWEEN

Syntax

MONTHS_BETWEEN
(
  date1  IN date,
  date2  IN date
)
RETURNS numeric;

MONTHS_BETWEEN
(
  timestamptz1  IN timestamptz,
  timestamptz2  IN timestamptz
)
RETURNS numeric;

Overview

The MONTHS_BETWEEN function calculates and returns the number of months between two dates (or timestamps).

The year, month, and day of two dates are compared to calculate the difference in months. If both dates are the last day of the month, it calculates as an integer number of months.

Otherwise, the difference in days divided by 31 is returned with the decimal point added. In other words, the decimal point value is calculated based on 31 days.

Parameters

Parameter	Description
date1 / timestamptz1	date / timestamptz type: the date (or timestamp) value that the comparison is based on.
date2 / timestamptz2	date / timestamptz type: date1/timestamptz1 to compare to the target date (or timestamp)

Example

# test 1
SELECT oracle.MONTHS_BETWEEN('2023-05-15'::date, '2022-01-10'::date);

  months_between  
------------------
 16.1612903225806
(1 row)

# test 2
SELECT oracle.MONTHS_BETWEEN('2023-05-15 12:00:00+09'::timestamptz, '2022-01-10 08:30:00+09'::timestamptz);

  months_between  
------------------
 16.1659946143627