CODEWITHSUNDEEP
hadoophiveimpala
Aman
Aman

Reputation: 3261

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

Upvotes: 1

Views: 1094

Answers (2)

Sesha Savarala
Sesha Savarala

Reputation: 1

convert the dates into same format to_date(table2.date) = to_date(t1.date)

Upvotes: -1

K S Nidhin
K S Nidhin

Reputation: 2650

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.

Upvotes: 3

Related Questions