Calculating number of days between two dates in HIVE

I need to calculate the number of days between two dates by ID in HIVE.

Current table:
ID Date1 Date2
10 1/14/2020 1/15/2020
10 5/10/2020 5/14/2020
20 3/1/2020 3/31/2020

The desired table would have am extra column with DayDiff of 1, 5, 31

Any sample HIVE code to accomplish this would be great.

Solution

We’ll use the DATEDIFF() function. Here’s the query you would write:

SELECT 
   DATEDIFF(Date_2, Date_1) 
     AS days FROM TableName;

Real Time Implementation https://learnsql.com/cookbook/how-to-find-the-number-of-days-between-two-dates-in-mysql/#:~:text=Use%20the%20DATEDIFF()%20function%20to%20retrieve%20the%20number%20of,%2C%20it's%20the%20expiration_date%20column.)
Use the DATEDIFF() function to retrieve the number of days between two dates in a MySQL database. This function takes two arguments:

The end date. (In our example, it’s the Date_2 column.) The start date. (In our example, it’s the Date_1 column.) These arguments can be date/datetime values, expressions that return date/datetime values, or columns of the datetime or date data type.

This function subtracts the start date from the end date and returns the number of days as an integer. In our example, butter was purchased on ‘2018-07-30’, but its Date_2 Date was ‘2019-08-10’; after Date_1, it could be eaten during 376 days. Notice that yogurt was purchased as an outdated product: the difference in days is -1 and its Date_1 date is later than its Date_2 date.