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.

Casting integers as timestamps

Highlighted

Casting integers as timestamps

New Contributor

Hi

 

I have 2 tables, both with a date field I want to join on. One table has the date field stored as an integer (20170101 for example as 1st January) and the other has it stored as a string (2017-01-01).

 

I am joining using cast(a.date_filed as timestamp) = b.cast(b.date_field as timestamp)

 

The join fails because on the first table (where the field is stored as an integer) the cast function doesn't convert correctly, presumably because it is reading the fuction as 20,170,101.

 

Is there a way I can convert this field into a timestamp format? 

 

Thanks

Don't have an account?
Coming from Hortonworks? Activate your account here