Help Center/ Data Lake Insight/ Spark SQL Syntax Reference/ Built-In Functions/ Date Functions/ months_between
Updated on 2023-10-25 GMT+08:00
View PDF

months_between

This function returns the month difference between date1 and date2.

Syntax

months_between(string date1, string date2)

Parameters

Table 1 Parameters

Parameter

Mandatory

Type

Description

date1

Yes

DATE

or

STRING

Minuend

The following formats are supported:
  • yyyy-mm-dd
  • yyyy-mm-dd hh:mi:ss
  • yyyy-mm-dd hh:mi:ss.ff3

date2

Yes

DATE

or

STRING

Subtrahend

The following formats are supported:
  • yyyy-mm-dd
  • yyyy-mm-dd hh:mi:ss
  • yyyy-mm-dd hh:mi:ss.ff3

Return Values

The return value is of the DOUBLE type.

  • If the values of date1 and date2 are not of the DATE or STRING type, the error message "data type mismatch" is displayed.
  • If the values of date1 and date2 are of the DATE or STRING type but are not in one of the supported formats, NULL is returned.
  • If date1 is later than date2, the return value is positive. If date2 is later than date1, the return value is negative.
  • If date1 and date2 correspond to the last day of two different months, an integer month is returned. Otherwise, the calculation is based on the number of days between date1 and date2 divided by 31.
  • If the value of date1 or date2 is NULL, NULL is returned.

Example Code

The value 0.0563172 is returned.

select months_between('2023-08-16 10:54:00', '2023-08-14 17:00:00');

The value 0.06451613 is returned.

select months_between('2023-08-16','2023-08-14');

The value NULL is returned.

select months_between('2023-08-16',null);
Parent topic: Date Functions