How to convert the date July 1, 2017 to dd-MM-yyyy using Hive SQL?

Question

I have a Hive table with a Week column having values such as:

I have to convert this field to a date format such as: 2017-07-01 (yyyy-MM-dd) using hive SQL. Any suggestions?

Answer 1

Use a combination of unix_timestamp and from_unixtime

select from_unixtime(unix_timestamp(week,'MMMM dd, yyyy'),'yyyy-MM-dd') from table_name;

unix_timestamp(string datetime, string pattern) converts datetime with given pattern to unix time stamp.

from_unixtime(bigint unixtime[, string format]) converts the number of seconds from unix epoch.