tricoder
tricoder

Reputation: 33

to change date from string to date type in hive

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

Answers (2)

mariusz-s
mariusz-s

Reputation: 1766

I would try two options:

  1. Check if simple replace works:

TO_DATE(from_unixtime(UNIX_TIMESTAMP(replace('07/03/2013', '/', '-'), 'dd-MM-yyyy')))

  1. Take a look at date documentation to check if any function will work for you

Upvotes: 0

David דודו Markovitz
David דודו Markovitz

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

Related Questions