string to date - hive SQL

Question

I am running queries in a hive environment.

I have a column which has a timestamp but is set up a string in the tables. I tried the following : all of them return Null

SELECT 
,To_date(activitydate)

Cast:

,cast(activitydate as timestamp)

This is the how the data is set up in the table:

Appreciate any inputs on how I can convert this :

05/12/2017 00:00:00     



SELECT 
 cust_id
,to_date(activitydate)  activity_date
,type type_of_contact


FROM repl_task

WHERE to_date(activitydate) BETWEEN '2014-01-01' AND '2017-01-01' ;

I am running out of memory if I run this :

SELECT 
 cust_id
,activitydate
,SUBSTR(activitydate,4,2) AS DT
,SUBSTR(activitydate,0,2) AS MONTH 
,SUBSTR(activitydate,7,4) AS YEAR
,type 


FROM task 

WHERE activitydate >='01/01/2016 00:00:00'

Answer 1

unix_timestamp function converts given format to unix timestamp and from_unixtime function converts from unix timestamp to given format:

hive> select from_unixtime(unix_timestamp('01/01/2016 00:00:00','MM/dd/yyyy HH:m:ss'),'yyyy-MM-dd');
OK
2016-01-01
Time taken: 0.118 seconds, Fetched: 1 row(s)