months_between function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns the number of months elapsed between dates or timestamps in expr1 and expr2.

Syntax

months_between(expr1, expr2 [, roundOff] )

Arguments

  • expr1: An DATE or TIMESTAMP expression.
  • expr2: An expression of the same type as expr1.
  • roundOff: A optional BOOLEAN expression.

Returns

A DOUBLE.

If expr1 is later than expr2, the result is positive.

If expr1 and expr2 are on the same day of the month, or both are the last day of the month, time of day is ignored. Otherwise, the difference is calculated based on 31 days per month, and rounded to 8 digits unless roundOff=false.

Examples

> SELECT months_between('1997-02-28 10:30:00', '1996-10-30');
 3.94959677
> SELECT months_between('1997-02-28 10:30:00', '1996-10-30', false);
 3.9495967741935485