Hi Folks, Im working on a hive query where i need to convert a string which is a date and its format is : 9/1/2016 11:31:21 AM. I need to convert into another format which looks like this 2016-09-01 11:31:21 AM. Kindly guide and help me in this regard?

[#1]

@Mayank Pandey there are some ways of converting the date. For example something like select inp_dt, from_unixtime(unix_timestamp(substr(inp_dt,0,11),'dd-MMM-yyyy')) as todateformat from table;

There are several ways to attempt this if you do a google search on your needs.

[#2]

Hi @Ian Roberts,

I tried the following query which you have suggested but I'm getting very different and weird date.

Select log_source_time, from_unixtime(unix_timestamp((test_date),'mm/dd/yyyy hh:mm:ss a'),'yyyy-mm-dd hh:mm:ss a') as todateformat from table1 limit 2;

log_source_time	todateformat
9/1/2016 11:31:21 AM	2016-31-01 11:31:21 AM
9/2/2016 9:59:59 AM	2016-59-02 09:59:59 AM

[#3]

@Mayank Pandey what does the following produce?

select log_source_time, from_unixtime(unix_timestamp(substr(log_source_time,0,11),'dd-MMM-yyyy')) as todateformat from table1 limit 2;

[#4]

@Ian Roberts the above query gave the todateformat output as NULL.

[#5]

oops, your input is slashes, not dashes. Can you try with the needed format?

[#6 · Accepted solution]

you have to specify MM for the month. 'mm' is for the minutes.

[#7]

@njayakumar the solution provided by you has worked. Thank a ton 🙂