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.

Convert date from different format to one particular format

Solved Go to solution

Convert date from different format to one particular format

How to insert data into hive table in a particular date format (DD/MM/YY) from the below hive table

1904287Christopher Rodriguez

Jan 11, 2003

96391595Thomas Stewart617/1969
2236067John Nelson08/22/54
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Convert date from different format to one particular format

Super Guru

Apologies for grammar and typos... writing this from my phone.

If your date format within a given data set is inconsistent the i would write a UDF to handle this. Inside the UDF you would have to detect the type of date you are working with using regex for example. This is done very nicely witb NiFi if you want yo hit the wasy button. If the format is consistent within a dataset yet different amount others then simply write a hive or pig script for each dataset and then parse out the date with the format you expect for that specific data set.

View solution in original post

9 REPLIES 9
Highlighted

Re: Convert date from different format to one particular format

Super Guru

Apologies for grammar and typos... writing this from my phone.

If your date format within a given data set is inconsistent the i would write a UDF to handle this. Inside the UDF you would have to detect the type of date you are working with using regex for example. This is done very nicely witb NiFi if you want yo hit the wasy button. If the format is consistent within a dataset yet different amount others then simply write a hive or pig script for each dataset and then parse out the date with the format you expect for that specific data set.

View solution in original post

Highlighted

Re: Convert date from different format to one particular format

Hey Sunile thanks for your reply could you help me with date udf have less idea about that if you could write here or you can email me niraj.tandel89@gmail.com

Highlighted

Re: Convert date from different format to one particular format

Super Guru

@Neeraj Tandel I would start by looking at the apache site and looking for UDF samples. Tons of information on the web on how to build simple UDF. I would start there and build out your UDF.

Highlighted

Re: Convert date from different format to one particular format

Thank you Sunil I really appreciate your efforts. I am kind of newbie in this stuff I have searched alot. Thanks once again for your time.

Highlighted

Re: Convert date from different format to one particular format

Super Guru
Highlighted

Re: Convert date from different format to one particular format

@Sunile Manjee Can you help me write date udf

Highlighted

Re: Convert date from different format to one particular format

Expert Contributor

We would have to create the column as a date field instead of a string field, to store dates. That way everything would be stored as a date datatype. So once you have that determined, your ingest data can use a UDF like @Sunile Manjee suggested. If you have data being stored as the date into hive, you could use any of the hive functions to represent the data in any way you prefer.

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions

search for Date Functions

Highlighted

Re: Convert date from different format to one particular format

Hey Satish Thanks for your Answer i dont know if this is right to say but could you provide an example (may be some coding stuffs) on how to proceed ?

Highlighted

Re: Convert date from different format to one particular format

New Contributor

This is a kind of a late response to this thread but you can use a coalesce function:


coalesce(from_unixtime(unix_timestamp(dob ,"MMM dd, yyyy"), "dd/MM/yy"),from_unixtime(unix_timestamp(dob ,"MM/dd/yyyy"), "dd/MM/yy"), from_unixtime(unix_timestamp(dob ,"MM/dd/yy"), "dd/MM/yy)) as new_dob
Don't have an account?
Coming from Hortonworks? Activate your account here