Convert date from string to date type in Hive

Question

I want to change string which is in format '29-MAR-17' to date type in Hive. The column in question is named "open_time".

I have tried using:

SELECT TO_DATE(from_unixtime(UNIX_TIMESTAMP('open_time', 'dd/MM/yyyy')));

But it returns NULL. Subsequently, my objectif is to do something like this :

SELECT * FROM table_hive WHERE open_time BETWEEN '29-MAR-17' AND '28-MAR-17';

With strings, it will definitely not work. Any help please ?

Answer 1

This should work

select to_date(from_unixtime(unix_timestamp('29-MAR-17','dd-MMM-yy')))

Returns 2017-03-29