Hive Date Functions – all possible Date operations

Handling DATE in HIVE is not as easy as compared to many traditional RDBMS in the market. One of the reason why I feel so is the inability of HIVE to throw proper error when dealing with wrong format. If any function or date format is not compatible, it will give NULL as output in place of error. This makes identification of wrong date manipulation in HIVE more tricky.
We have used DATE_FORMAT which is available from Hive 1.2 onwards. There is more than one way to achieve same result and we have listed it where ever possible. Also you can use your own methods to get desired result like for adding a year you can try to add 12 months in place of a year.You can use most of the DATE FORMAT combinations together to change DATE into required format. For more details kindly check our previous post: Handle Date and Timestamp in HIVE like a pro
Let’s look at the DATE operations which you can do in HIVE:

S#TitleDescriptionCodeOutput
1Current DateHow to select present dateselect current_date;2018-06-15
2Change Date FormatDisplay date in some other date formatselect date_format(current_date,'MM/dd/YYYY');06/15/2018
3Change Date FormatDisplay Month Name in Dateselect date_format(current_date,'dd-MMM-YYYY');15-Jun-2018
4Change Date FormatDisplay full Month Name in Dateselect date_format(current_date,'dd-MMMM-YYYY');15-June-2018
5Day of the WeekDisplay Day Name of the weekselect date_format(current_date,'E');Fri
6Day of the WeekDisplay Day Name(Full) of the weekselect date_format(current_date,'EEEE');Friday
7Day of the WeekDisplay Day Number of the week (1=Mon..5=Fri..7=Sun)select date_format(current_date,'u');5
8Day of the YearDisplay Day of the Year out of 365/366select date_format(current_date,'D');166
9Day Occurrence of the MonthDisplay occurrence of day in the month like 3rd Friday of monthselect date_format(current_date,'YYYY-MM-dd EEEE(F)');2018-06-15 Friday(3)
10Week of the yearDisplay which week of the year date belongs to select date_format(current_date,'w');24
11Week of the monthDisplay which week of the month date belongs to select date_format(current_date,'W');3
12TimeZone infoDisplay current TIMEZONE infoselect date_format(current_date,'z:Z');UTC:+0000
13YearDisplay Year from the dateselect year(current_date);2018
14MonthDisplay Month from the dateselect month(current_date);6
15DayDisplay Day from the date select day(current_date);15
16Week of the yearDisplay which week of the year date belongs to select weekofyear(current_date);24
17Add DaysDisplay date after adding days to itselect date_add(current_date,2);2018-06-17
18Add DaysDisplay date after adding days to itselect date_format(current_date + interval '2' day,'YYYY-MM-dd');2018-06-17
19Add MonthsDisplay date after adding months to itselect add_months(current_date,2);2018-08-15
20Add MonthsDisplay date after adding months to itselect date_format(current_date + interval '2' month,'YYYY-MM-dd');2018-08-15
21Add YearDisplay date after adding year/sselect date_format(current_date + interval '2' year,'YYYY-MM-dd');2020-06-15
22Add YearDisplay date after adding year (or 12 months)select add_months(current_date,12);2019-06-15
23Subtract DaysDisplay date after subtracting days to itselect date_add(current_date,-2);2018-06-13
24Subtract DaysDisplay date after subtracting days to itselect date_sub(current_date,2);2018-06-13
25Subtract DaysDisplay date after subtracting daysselect date_format(current_date - interval '2' day,'YYYY-MM-dd');2018-06-13
26Subtract DaysDisplay date after subtracting daysselect date_format(current_date + interval '-2' day,'YYYY-MM-dd');2018-06-13
27Subtract MonthsDisplay date after subtracting monthsselect add_months(current_date,-2);2018-04-15
28Subtract MonthsDisplay date after subtracting monthsselect date_format(current_date + interval '-2' month,'YYYY-MM-dd');2018-04-15
29Subtract MonthsDisplay date after subtracting monthsselect date_format(current_date - interval '2' month,'YYYY-MM-dd');2018-04-15
30Subtract YearDisplay date after subtracting Yearselect date_format(current_date + interval '-2' year,'YYYY-MM-dd');2016-06-15
31Subtract YearDisplay date after subtracting Year (12 months)select add_months(current_date,-12);2017-06-15
32Subtract 2 datesDisplay difference of 2 datesselect datediff('2018-12-31',current_date);199

I will continue to update this table with more operations like timestamp handling etc. However if you are looking for anything specific, let me know by dropping a comment. I will try to address that quickly.
Hope this helps.

Leave a Reply

Your email address will not be published. Required fields are marked *