CODEWITHSUNDEEP
datehivetimestamphiveqlimpala
Yags
Yags

Reputation: 522

Changing dd/mm/yyyy/ hh/mm/ss format to yyyymm in Hive

I'm using Hive at the moment. I have a column (column A) of strings which is in the following format 11/9/2009 0:00:00. I'd like to extract the yyyymm. i.e. I'd like the above string to be 200909. I've tried two different methods none of them worked.

I have tried to convert the string using two different methods

       concat(year(Column A),lpad(month(Column A),2,0))


       convert(datetime, Column A)

For the first row of code I'm receiving : NULL in all rows

For the second one I'm receiving :

Encountered: DATETIME Expected: ALL, CASE, CAST, DEFAULT, DISTINCT, EXISTS, FALSE, IF, INTERVAL, NOT, NULL, REPLACE, TRUNCATE, TRUE, IDENTIFIER CAUSED BY: Exception: Syntax error

Upvotes: 2

Views: 5089

Answers (2)

leftjoin
leftjoin

Reputation: 38290

Use unix_timestamp(string date, string pattern) to convert given date format to seconds passed from 1970-01-01. Then use from_unixtime() to convert to required format:

select  from_unixtime(unix_timestamp( '11/9/2009 0:00:00','dd/MM/yyyy HH:mm:ss'), 'yyyyMM');

Result:

200909

Read also: Impala data and time functions and Hive date functions.

One more solution, works in Hive: 

select  concat(regexp_extract('11/9/2009 0:00:00','(\\d{1,2})/(\\d{1,2})/(\\d{4})',3),lpad(regexp_extract('11/9/2009 0:00:00','(\\d{1,2})/(\\d{1,2})/(\\d{4})',2),2,0))

Upvotes: 2

Yags
Yags

Reputation: 522

Since I'm trying to turn strings into YYYYMM I have to use the below, which worked for me:

      'concat(substr(Column A, instr(Column A, ' ')-4, 4),substr(Column A, instr(Column A, ' /')+1, 2))'

Upvotes: 1

Related Questions