MONTHS_BETWEEN

Syntax

MONTHS_BETWEEN(date1, date2)

Arguments

date1, date2: A valid date, datetime, or parsable date string. Only the date part is used.

Return Type

Integer. If date1 or date2 is not a valid date, returns NULL.

Examples

SQL

SELECT MONTHS_BETWEEN(DATE('2017-03-01'), DATE('2017-01-01'));

+--------------------------------------------------------+
| MONTHS_BETWEEN(DATE('2017-03-01'), DATE('2017-01-01')) |
+--------------------------------------------------------+
|                                                      2 |
+--------------------------------------------------------+

If date1 is in the past, the function returns a negative result.

SQL

SELECT MONTHS_BETWEEN(DATE('2017-01-01'), DATE('2017-03-01'));

+--------------------------------------------------------+
| MONTHS_BETWEEN(DATE('2017-01-01'), DATE('2017-03-01')) |
+--------------------------------------------------------+
|                                                     -2 |
+--------------------------------------------------------+

NOW() can be used as one of the parameters.

SQL

SELECT MONTHS_BETWEEN('2017-10-31',NOW());

+------------------------------------+
| MONTHS_BETWEEN('2017-10-31',now()) |
+------------------------------------+
|                  2.741935483870968 |
+------------------------------------+

Related Topics

MONTH