MONTHS_BETWEEN

The MONTHS_BETWEEN function returns the number of months between two dates.

Syntax

MONTHS_BETWEEN (date1, date2)

Remarks

•

If the later date is first, the result is a positive number.

•

If the earlier date is first, the result is a negative number. The number returned is also based on the real calendar.

•

If the result is not a whole number of months (that is, there are some days as well), the days part is shown as a decimal (for example, 0.5 months for 15 days out of a 30-day month).

•

The number is not rounded.

•

Hive's MONTHS_BETWEEN rounds off the result to 8 digits decimal.

•

The result is a numeric data type.

Example

MONTHS_BETWEEN (sysdate, TO_DATE ('01-01-2007','dd-mm-yyyy'))

This returns the number of months since January 1, 2007.