How to change date format in hive?

To convert date string from one format to another you have to use two date function of hive

1. unix_timestamp(string date, string pattern) convert time stringwith given pattern to unix time stamp (in seconds), return 0 iffail.
2. from_unixtime(bigint unixtime[, string format]) converts thenumber of seconds from unix epoch (1970-01-01 00:00:00 UTC) to astring representing the timestamp of that moment in the currentsystem time zone.

Using above two function you can achieve your desired result.

The sample input and output can be seen from below image:

The final query is

select from_unixtime(unix_timestamp('2016/06/01','yyyy/MM/dd'),'yyyy-MM-dd') from table1; 

where table1 is the table name present in my hive database.

I hope this help you!!!

Let's say you have a column 'birth_day' in your table which is in your format,you should use the following query to convert birth_day into the required format.

date_Format(birth_day, 'yyyy-MM-dd')

You can use it in a query in the following way

select * from yourtablewhere date_Format(birth_day, 'yyyy-MM-dd') = '2019-04-16';

Use :

unix_timestamp(DATE_COLUMN, string pattern)

The above command would help convert the date to unix timestamp format which you may format as you want using the Simple Date Function.

Date Function