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#TitleCodeOutputDescription
1Current Dateselect current_date;2018-06-15How to select present date
2Change Date Formatselect date_format(current_date,'MM/dd/YYYY');06/15/2018Display date in some other date format
3Change Date Formatselect date_format(current_date,'dd-MMM-YYYY');15-Jun-2018Display Month Name in Date
4Change Date Formatselect date_format(current_date,'dd-MMMM-YYYY');15-June-2018Display full Month Name in Date
5Day of the Weekselect date_format(current_date,'E');FriDisplay Day Name of the week
6Day of the Weekselect date_format(current_date,'EEEE');FridayDisplay Day Name(Full) of the week
7Day of the Weekselect date_format(current_date,'u');5Display Day Number of the week (1=Mon..5=Fri..7=Sun)
8Day of the Yearselect date_format(current_date,'D');166Display Day of the Year out of 365/366
9Day Occurrence of the Monthselect date_format(current_date,'YYYY-MM-dd EEEE(F)');2018-06-15 Friday(3)Display occurrence of day in the month like 3rd Friday of month
10Week of the yearselect date_format(current_date,'w');24Display which week of the year date belongs to
11Week of the monthselect date_format(current_date,'W');3Display which week of the month date belongs to
12TimeZone infoselect date_format(current_date,'z:Z');UTC:+0000Display current TIMEZONE info
13Yearselect year(current_date);2018Display Year from the date
14Monthselect month(current_date);6Display Month from the date
15Dayselect day(current_date);15Display Day from the date
16Week of the yearselect weekofyear(current_date);24Display which week of the year date belongs to
17Add Daysselect date_add(current_date,2);2018-06-17Display date after adding days to it
18Add Daysselect date_format(current_date + interval '2' day,'YYYY-MM-dd');2018-06-17Display date after adding days to it
19Add Monthsselect add_months(current_date,2);2018-08-15Display date after adding months to it
20Add Monthsselect date_format(current_date + interval '2' month,'YYYY-MM-dd');2018-08-15Display date after adding months to it
21Add Yearselect date_format(current_date + interval '2' year,'YYYY-MM-dd');2020-06-15Display date after adding year/s
22Add Yearselect add_months(current_date,12);2019-06-15Display date after adding year (or 12 months)
23Subtract Daysselect date_add(current_date,-2);2018-06-13Display date after subtracting days to it
24Subtract Daysselect date_sub(current_date,2);2018-06-13Display date after subtracting days to it
25Subtract Daysselect date_format(current_date - interval '2' day,'YYYY-MM-dd');2018-06-13Display date after subtracting days
26Subtract Daysselect date_format(current_date + interval '-2' day,'YYYY-MM-dd');2018-06-13Display date after subtracting days
27Subtract Monthsselect add_months(current_date,-2);2018-04-15Display date after subtracting months
28Subtract Monthsselect date_format(current_date + interval '-2' month,'YYYY-MM-dd');2018-04-15Display date after subtracting months
29Subtract Monthsselect date_format(current_date - interval '2' month,'YYYY-MM-dd');2018-04-15Display date after subtracting months
30Subtract Yearselect date_format(current_date + interval '-2' year,'YYYY-MM-dd');2016-06-15Display date after subtracting Year
31Subtract Yearselect add_months(current_date,-12);2017-06-15Display date after subtracting Year (12 months)
32Subtract 2 datesselect datediff('2018-12-31',current_date);199Display difference of 2 dates

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.

6 thoughts on “Hive Date Functions – all possible Date operations

  1. how can i convert a date like ’05-JAN-20′ to 20200105 (‘yyyymmdd’)?

    i have been on this for days. This is not working

    CAST(date_Format(’05-JAN-20′, ‘yyyymmdd’) AS BIGINT)

    1. Hi Max

      Apologies for delay in reply.
      Please try below:
      select from_unixtime(unix_timestamp(’05-JAN-20′, ‘dd-MMM-yy’),’yyyyMMdd’);

      date_format will only work when you input date is in format ‘yyyy-MM-dd’. If it is in any other format , it will result in NULL output or error. If input is in other format, you have to try from_unixtime & unix_timestamp.

      Hope this helps.

      Best,
      Raj

  2. Hi,
    How can I fetch the 15th previous working date (yyyyMMdd) – excluding all the Saturdays and Sundays.
    Lets say it is 20th feb 2020 today; I want 20200131 as output.

  3. Hi,
    How can I fetch the 15th previous working date (yyyyMMdd) – excluding all the Saturdays and Sundays.
    Lets say it is 20th feb 2020 today; I want 20200131 as output.
    I want this in HIVE.

    1. I am sorry, I missed the details over my requirement.

      I have a table with date column – date format is in yyyyMMdd (string). I want to filter data from this table everyday for the 15th previous working day. need to get the value from the date column in my table which satisfies this condition.

  4. Fetching the 15th last working day date-yyyyMMdd (excluding only weekends) in Hive

    I have a table with date column (date in string format yyyyMMdd). My requirement is to design a logic to fetch data from the table where “date column value equals to the date of the 15th previous working day” (excluding only Saturdays and Sundays) without using a UDF or a shell script. For example it is 21st Feb 2020 today; the logic should produce an output: 20200203.

Leave a Reply

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