Identify Months Between Two Dates – Apache Hive & Impala

The function MONTHS_BETWEEN returns the number of months between the dates specified. If the first expression is later than the second, the outcome is positive, otherwise negative. This can usually be used in business situations, for example to assess whether a given number of months has elapsed or whether a certain end-of-month date has been met. 

The outcome of this function may contain a fractional part reflecting additional days in addition to the complete months between the periods. The fractional variable is determined by dividing the difference in days by 31. (regardless of the month). If expr1 and expr2 are either the same days of the month or the last two days of the month, the result is always an integer.

This is equivalent to Oracle’s MONTHS_BETWEEN function and available in both Apache Hive and Apache Impala.

Examples:
SELECT MONTHS_BETWEEN(‘2021-02-28 10:30:00’, ‘2020-10-30’)
This returns 3.94959677

SELECT MONTHS_BETWEEN (‘2021-02-02′,’2021-01-01’)
This returns 1.03225806

SELECT MONTHS_BETWEEN (‘2021-01-01’, ‘2021-03-14’)
This returns -2.41935483

SELECT MONTHS_BETWEEN (‘2021-02-02’, ‘2021-01-02’)
This returns 1

Hope you find this article helpful.

Do follow for more interesting updates.