Conversion Hive datediff() to months

Question

When using Hive there is the functionality to return the select datediff('date1','date2') from table value which returns the number of days between two dates. What if I would be interested in the months between the two dates?

One option would be to divide the result by 30.5, as the average months has approximately 30.5 days, but this would certainly yield an inprecision when large date ranges are considered.

Do you know a way of retrieving the number of months (rather than the number of days) in a similar fashion with standard SQL (ideally HIVE) syntax?

Solution

You can try with:

SELECT CAST(MONTHS_BETWEEN(date1, date2) AS INT) as numberOfMonthsBetweenDates
FROM table

This will return your expected result.