How to convert datetime in string format in hadoop to short date?

0
votes

I'm using the following the query to get the date out of string (20191101154559) in Hadoop(hive).

select max(cast(to_date(from_unixtime(unix_timestamp(substr(column3,1,8), 'yyyymmdd'))) as date)) as dt from databasea.table_name_b;

The output i'm getting after running the above script is '31/01/2019' where as the table holds dates of nov 2019. Not sure where i'm going wrong here.

Appreciate if anyone can look into this issue.

1
hadoop

1 Answers

0
votes

from_unixtime you can use it to get in 'yyyymmdd' or 'yyyy-MM-dd' whatever format you want data in. For Eg - select from_unixtime(unix_timestamp(substr('20191101154559',1,8), 'yyyymmdd'),'yyyymmdd');

This will return 20191101