The MONTHS_BETWEEN function returns the number of months between date1 and date2.
Syntax
MONTHS_BETWEEN(date1,date2)
Parameters
| Parameter | Description |
|---|---|
| date1 | A value of the DATE data type. |
| date2 | A value of the DATE data type. |
Return type
The return type is NUMBER. If date1 is greater than date2, this function returns a positive number. If date1 is less than date2, this function returns a negative number.
Examples
The following statement queries the number of months between the current time and a specified time:
SELECT SYSDATE,
MONTHS_BETWEEN(SYSDATE,TO_DATE('2006-01-01','YYYY-MM-DD')),
MONTHS_BETWEEN(SYSDATE,TO_DATE('2022-01-01','YYYY-MM-DD'))
FROM DUAL;
The following result is returned:
+---------------------+------------------------------------------------------------+------------------------------------------------------------+
| SYSDATE | MONTHS_BETWEEN(SYSDATE,TO_DATE('2006-01-01','YYYY-MM-DD')) | MONTHS_BETWEEN(SYSDATE,TO_DATE('2022-01-01','YYYY-MM-DD')) |
+---------------------+------------------------------------------------------------+------------------------------------------------------------+
| 2020-03-08 15:38:35 | 170.246832063918757467144563918757467145 | -21.75316793608124253285543608124253285544 |
+---------------------+------------------------------------------------------------+------------------------------------------------------------+