Search code examples
hadoophiveimpala

Parsing date format to join in hive

I have a date field which is of type String and in the format: 

03/11/2001

And I want to join it with another column, which is in a different String format:

1855-05-25 12:00:00.0

How can I join both columns efficiently in hive, ignoring the time part of the second column?

My query looks like below: 

LEFT JOIN tabel1 t1 ON table2.Date=t1.Date
Solution

  • Since you have both the date values in different formats you need to use the date functions for both and convert it to a similar format of date type in your join query. It would be something like this :

    LEFT JOIN tabel1 t1 ON unix_timestamp(table2.Date, 'yyyy-MM-dd HH:mm:ss.S')table2.Date=unix_timestamp(t1.Date,'MM/dd/yyyy')

    You could refer this and this for the hive in built date functions.