Convert date from different format to one particular format

[#1]

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

1904287	Christopher Rodriguez	Jan 11, 2003
96391595	Thomas Stewart	617/1969
2236067	John Nelson	08/22/54

[#2 · Accepted solution]

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.

[#3]

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

[#4]

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

[#5]

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.

[#6]

@Neeraj Tandel here is good example

http://www.hadooptpoint.com/how-to-write-hive-udf-example-in-java/

[#7]

@Sunile Manjee Can you help me write date udf

[#8]

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

[#9]

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 ?

[#10]

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