question Re: 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? in Archives of Support Questions (Read Only)

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?

pmayank02 — Mon, 12 Sep 2016 18:31:36 GMT

Re: 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?

iroberts — Mon, 12 Sep 2016 20:15:19 GMT

@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.

Re: 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?

pmayank02 — Mon, 12 Sep 2016 21:32:09 GMT

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

Re: 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?

iroberts — Mon, 12 Sep 2016 21:42:11 GMT

@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;

Re: 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?

pmayank02 — Mon, 12 Sep 2016 21:47:38 GMT

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

Re: 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?

iroberts — Mon, 12 Sep 2016 22:14:38 GMT

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

Re: 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?

njayakumar — Mon, 12 Sep 2016 23:22:56 GMT

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

Re: 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?

pmayank02 — Tue, 13 Sep 2016 12:25:31 GMT

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