Change date from string type to date in hive
I want to change the string that is in the format dd/mm/yyyy
before the date in the hive.
I am using hive version 1.0.0. I've tried using:
TO_DATE(from_unixtime(UNIX_TIMESTAMP('07/03/2013', 'dd/mm/yyyy')))
But it returns NULL
. Although it works with the format 'dd-mm-yyyy'
. But it returns NULL with 'dd/mm/yyyy'
.
I tried to use too CAST
, but it also doesn't give me the correct result.
+3
source to share
2 answers
M - month
m - minutes
hive> select TO_DATE(from_unixtime(UNIX_TIMESTAMP('07/03/2013', 'dd/MM/yyyy'))) ;
OK
2013-03-07
or
hive> select TO_DATE(from_unixtime(UNIX_TIMESTAMP('07/03/2013', 'd/M/y'))) ;
OK
2013-03-07
or
hive> select cast (regexp_replace('07/03/2013','(..)/(..)/(....)','$3-$2-$1') as date);
OK
2013-03-07
+4
source to share
I would try two options:
- Check if a simple replacement works:
TO_DATE (from_unixtime (UNIX_TIMESTAMP (replace ('07 / 03/2013 ',' / ',' - '),' dd-MM-yyyy ')))
- Have a look at the date documentation to check if any feature will work.
0
source to share