How to delete "" and change it to date format

user_14488716 0
Viewed 82

My dataset's date column (tweet_stamp) is shown as "2020-01-29 00-21-29" and it is STRING format.

I would like to have result as 2020-01-29.

How to delete "" in string and change it to date format?

I tried code as below

select to_date(from_unixtime(unix_timestamp(tweet_timestamp,'"yyyy-MM-dd 00-00-00"'), 'yyyy-MM-dd')) as tweet_date;

However, result is NULL.

hiveql hive date sql
1 Answers

You do not need unix_timestamp+from_unix_time conversion because date part of string is already in right format. Just remove double quotes, get substring and optionally convert to date:

select date(substr(regexp_replace('"2020-01-29 00-21-29"','"',''),1,10)) --returns 2020-01-29

Or even simpler using to_date function:

select to_date(regexp_replace('"2020-01-29 00-21-29"','"','')) --2020-01-29
user_27003440
Related