Reputation: 33
I want to change string which is in format dd/mm/yyyy
to date type in hive.
I am using hive version 1.0.0 . I have tried using:
TO_DATE(from_unixtime(UNIX_TIMESTAMP('07/03/2013', 'dd/mm/yyyy')))
But it returns NULL
. Although it works with format 'dd-mm-yyyy'
. But it returns NULL with 'dd/mm/yyyy'
.
I have tried using CAST
also but it also does not give me the correct result.
Upvotes: 3
Views: 15145
Reputation: 1766
I would try two options:
TO_DATE(from_unixtime(UNIX_TIMESTAMP(replace('07/03/2013', '/', '-'), 'dd-MM-yyyy')))
Upvotes: 0
Reputation: 44941
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
Upvotes: 6