CODEWITHSUNDEEP
prestoamazon-athena
Miguel Coder
Miguel Coder

Reputation: 1949

Converting values in Athena Presto

I have a few things I want to accomplish with Presto. I currently getting some data in the following formats

  1. date 16-Jan-2018
  2. num 1000

I want to write a query that can convert these values to

  1. 2018-01-16
  2. 1,000

Upvotes: 0

Views: 943

Answers (1)

user152468
user152468

Reputation: 3242

For the date you could do the following: 

select date_parse('date 16-Jan-2018','date %d-%b-%Y')

For the second field, you would have to split it first with split(string, delimiter), then cast the second array element to INTEGER.

Here is the full answer:

SELECT date_parse(date_string,'date %d-%b-%Y') as parsed_date, 
       CAST(
            split(int_string, ' ')[2] AS INTEGER
       ) as parsed_int 
  FROM (VALUES ('date 16-Jan-2018', 'int 1000')) 
    AS t(date_string, int_string)

Upvotes: 3

Related Questions