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.
Hive Date Format examples
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.
| S# | Title | SQL Code | Output | Description |
|---|---|---|---|---|
| 1 | Current Date | select current_date; | 2018-06-15 | How to select present date |
| 2 | Change Date Format | select date_format(current_date,’MM/dd/YYYY’); | 06/15/2018 | Display date in some other date format |
| 3 | Change Date Format | select date_format(current_date,’dd-MMM-YYYY’); | 15-Jun-2018 | Display Month Name in Date |
| 4 | Change Date Format | select date_format(current_date,’dd-MMMM-YYYY’); | 15-June-2018 | Display full Month Name in Date |
| 5 | Day of the Week | select date_format(current_date,’E’); | Fri | Display Day Name of the week |
| 6 | Day of the Week | select date_format(current_date,’EEEE’); | Friday | Display Day Name(Full) of the week |
| 7 | Day of the Week | select date_format(current_date,’u’); | 5 | Display Day Number of the week (1=Mon..5=Fri..7=Sun) |
| 8 | Day of the Year | select date_format(current_date,’D’); | 166 | Display Day of the Year out of 365/366 |
| 9 | Day Occurrence of the Month | select 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 |
| 10 | Week of the year | select date_format(current_date,’w’); | 24 | Display which week of the year date belongs to |
| 11 | Week of the month | select date_format(current_date,’W’); | 3 | Display which week of the month date belongs to |
| 12 | TimeZone info | select date_format(current_date,’z:Z’); | UTC:+0000 | Display current TIMEZONE info |
| 13 | Year | select year(current_date); | 2018 | Display Year from the date |
| 14 | Month | select month(current_date); | 6 | Display Month from the date |
| 15 | Day | select day(current_date); | 15 | Display Day from the date |
| 16 | Week of the year | select weekofyear(current_date); | 24 | Display which week of the year date belongs to |
| 17 | Add Days | select date_add(current_date,2); | 2018-06-17 | Display date after adding days to it |
| 18 | Add Days | select date_format(current_date + interval ‘2’ day,’YYYY-MM-dd’); | 2018-06-17 | Display date after adding days to it |
| 19 | Add Months | select add_months(current_date,2); | 2018-08-15 | Display date after adding months to it |
| 20 | Add Months | select date_format(current_date + interval ‘2’ month,’YYYY-MM-dd’); | 2018-08-15 | Display date after adding months to it |
| 21 | Add Year | select date_format(current_date + interval ‘2’ year,’YYYY-MM-dd’); | 2020-06-15 | Display date after adding year/s |
| 22 | Add Year | select add_months(current_date,12); | 2019-06-15 | Display date after adding year (or 12 months) |
| 23 | Subtract Days | select date_add(current_date,-2); | 2018-06-13 | Display date after subtracting days to it |
| 24 | Subtract Days | select date_sub(current_date,2); | 2018-06-13 | Display date after subtracting days to it |
| 25 | Subtract Days | select date_format(current_date – interval ‘2’ day,’YYYY-MM-dd’); | 2018-06-13 | Display date after subtracting days |
| 26 | Subtract Days | select date_format(current_date + interval ‘-2′ day,’YYYY-MM-dd’); | 2018-06-13 | Display date after subtracting days |
| 27 | Subtract Months | select add_months(current_date,-2); | 2018-04-15 | Display date after subtracting months |
| 28 | Subtract Months | select date_format(current_date + interval ‘-2′ month,’YYYY-MM-dd’); | 2018-04-15 | Display date after subtracting months |
| 29 | Subtract Months | select date_format(current_date – interval ‘2’ month,’YYYY-MM-dd’); | 2018-04-15 | Display date after subtracting months |
| 30 | Subtract Year | select date_format(current_date + interval ‘-2′ year,’YYYY-MM-dd’); | 2016-06-15 | Display date after subtracting Year |
| 31 | Subtract Year | select add_months(current_date,-12); | 2017-06-15 | Display date after subtracting Year (12 months) |
| 32 | Subtract 2 dates | select datediff(‘2018-12-31’,current_date); | 199 | Display difference of 2 dates |
For more details about different formats available kindly check our previous post: Handle Date and Timestamp in HIVE like a pro
Update:
DATE_FORMAT will give correct output when your input date is in format ‘yyyy-MM-dd’. If it is in any other format , it will result in NULL output or error. In order to handle such scenarios you have to use combination of from_unixtime & unix_timestamp.
select from_unixtime(unix_timestamp('05-JAN-20', 'dd-MMM-yy'),'yyyy-MM-dd');
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.
Hi,
How to get the output as Dec 31 of the Year prior to some pre-existing date value from a table.
Example: Data in a table is 5th March of 2020, 03/05/2020
And expecting an output as 31st of Dec 2019, 12/31/2019.
I was able to think of adding dateadd function to get the previous year but not able to figure out of adding Dec 31 as the date.
dateadd(year, -1, sp.start_date)
Hi Bhamini
I will recommend to hardcode it i.e. 31-Dec and concatenate with the year to make it date.
Hi,
I don’t know which format I get from source but I need to convert it into MMddYY
Raj,
What would be the best way to calculate the 1st of a month? For example, I want the 1st day of the month two months earlier. Today is 2020-09-02 minus 2 months is 2020-07-02 but I want 2020-07-01.
Would something like the below work?
select date_format( add_months( current_date, -2 ), ‘YYYY-MM-01’ );
Hi Sam,
Yes the approach you have mentioned will definitely work and thanks for sharing this.
You can also try TRUNC function to truncate date to first of month.
select trunc(add_months(current_date,-2),’MM’);
Best,
Raj
Hi,
How can I convert day to week starting Monday/Sunday
e.g. if I have dates(MM/DD/YYYY) as below –
08/03/2020
08/04/2020
08/05/2020
08/06/2020
08/07/2020
08/08/2020
08/09/2020
I expect 08/03/2020 for all of them, considering I am taking Monday as the start of the week.
Hi Sourav
Please try below:
next_day(date_sub(from_unixtime(unix_timestamp(col1,'MM/dd/yyyy'),'yyyy-MM-dd'),7),'Mo')*col1 is column in the table defined as string which stores date in the MM/dd/yyyy format.
So first you have to convert it into the preferred format which is yyyy-MM-dd and then apply date function on it.
**Change Mo to Tu,Su etc depending on the day you want to consider as start of the week.
—
Raj
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.
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.
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.
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.
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)
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