Conversion Hive Datediff() to Months
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...
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?
1 Answer
You can try with:
SELECT CAST(MONTHS_BETWEEN(date1, date2) AS INT) as numberOfMonthsBetweenDates
FROM table
This will return your expected result.
Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.
