CODEWITHSUNDEEP
prestoamazon-athena
Lucky
Lucky

Reputation: 383

How do I convert 20190801 to Aug-2019

I have a column which is an integer 201908. I need to out this as Aug-2019.

I know in SQl, its easy to do: If salesmonth is the name of the column

UPPER(FORMAT(cast(cast(salesmonth as varchar(10)) + '01' as date),'MMM-yyyy'))

How do I do this in Hive?

I converted salesmonth to varchar and added 01 to make it 20190801. I tried date_parse to %MMM-%yy but pretty sure I am not doing it right.

select salesmonth, concat(cast(salesmonthas varchar(10)),'01') as t1
,date_format(concat(cast(salesmonth as varchar(10)),'01'),'%MMM-%yyyy') as t2
from ${db['PL_SALES']} where salesmonth= 201908 limit 10

Expected Output: AUG-19

Error: Invalid_Function_Argument. Invalid Format: 20190801

EDIT:

This is easier to handle with Case statements - but I am hoping to avoid case and looking for a similar format as SQL.

Upvotes: 0

Views: 114

Answers (1)

Piotr Findeisen
Piotr Findeisen

Reputation: 20770

You can use date_parse to parse '20190801' into a date (actually a timestamp):

presto:default> SELECT date_parse('20190801', '%Y%m%d');
          _col0
-------------------------
 2019-08-01 00:00:00.000

You can then use date_format to render the required result:

presto:default> SELECT date_format(date_parse('20190801', '%Y%m%d'), '%b-%Y');
  _col0
----------
 Aug-2019

Or, if you want something like 'AUG-19':

presto:default> SELECT upper(date_format(date_parse('20190801', '%Y%m%d'), '%b-%y'));
 _col0
--------
 AUG-19

Upvotes: 3

Related Questions