SQL Reference - MONTHS

MONTHS_BETWEEN(date1, date2)

date1: The first parameter referring to the later date in the sequence of the two dates that are being compared.
date2: The second parameter referring to the earlier date which will be compared with the first date to calculate the number of months between them.

Example

SELECT MONTHS_BETWEEN(TO_DATE('2020-12-31', 'YYYY-MM-DD'),
                     TO_DATE('2020-01-01', 'YYYY-MM-DD')) AS months_diff
FROM dual;

Output

MONTHS_DIFF
----------------
      12

Explanation

The MONTHS_BETWEEN function in Oracle calculates the number of months between two dates. In the provided example, it calculates the number of months between ‘2020-12-31’ and ‘2020-01-01’. The result is 12, indicating that the two dates are 12 months apart.

The function signature for MONTHS_BETWEEN in PostgreSQL is as follows:MONTHS_BETWEEN(date1 DATE, date2 DATE) RETURNS FLOAT

date1: This parameter represents the first date value used in the calculation. It is of DATE type.
date2: This parameter represents the second date value used in the calculation. It is also of DATE type.
returns float: This indicates that the function will return the result as a float data type. This result represents the number of months between date1 and date2.

Example

SELECT MONTHS_BETWEEN(TIMESTAMP '2018-01-01',
                      TIMESTAMP '2018-04-01');

Output

Explanation

MONTHS_BETWEEN function returns the number of months between two dates. In this example, it determines the number of months between ‘2018-01-01’ and ‘2018-04-01’, which results in 3 months.