Conversion Hive datediff() to months

sql date hadoop hive datediff
Skybrush picture Skybrush · Mar 28, 2017 · Viewed 10k times · Source

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?

Answer

queise picture queise · Mar 28, 2017

You can try with:

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

This will return your expected result.

Related questions

How to convert a date format YYYY-MM-DD into integer YYYYMMDD in Presto/Hive?

How to CONVERT a date in format YYYY-MM-DD into integer YYYYMMDD in Presto/Hive? I am trying to convert the below list into YYYYMMDD integers WITH all_dates as (SELECT CAST(date_column AS DATE) date_column FROM (VALUES (SEQUENCE(…

Read More
How to return only the Date from a SQL Server DateTime datatype

SELECT GETDATE() Returns: 2008-09-22 15:24:13.790 I want that date part without the time part: 2008-09-22 00:00:00.000 How can I get that?

Read More
Select data from date range between two dates

I have a table Named Product_Sales and it holds data like this Product_ID | Sold_by | Qty | From_date | To_date -----------+---------+-----+------------+----------- 3 | 12 | 7 | 2013-01-05 | 2013-01-07 6 | 22 | 14 | 2013-01-06 | 2013-01-10 8 | 11 | 9 | 2013-02-05 | 2013-02-11 Now …

Read More