Support Questions
Find answers, ask questions, and share your expertise
Announcements
Check out our newest addition to the community, the Cloudera Innovation Accelerator group hub.

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

Contributor

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

View solution in original post

7 REPLIES 7

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.

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

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;

Explorer

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

Rising Star

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

Contributor

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

Explorer

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