Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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?

Solved Go to solution

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?

Explorer
 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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?

Contributor

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

View solution in original post

7 REPLIES 7
Highlighted

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?

Rising Star

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

Highlighted

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?

Explorer

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
Highlighted

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?

Rising Star

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

Highlighted

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?

Explorer

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

Highlighted

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?

Rising Star

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

Highlighted

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?

Contributor

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

View solution in original post

Highlighted

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?

Explorer

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

Don't have an account?
Coming from Hortonworks? Activate your account here