Is there a way to cast string to date to int in athena?

Question

I'm working on use case where i need to convert the string type to integer type in AWS Athena

Below is my input data -

year    month
2022    jan
2022    feb
2012    apr
2023    may
2019    jun

Below is what I'm expecting -

year    month   yearmon
2022    jan     202201
2022    feb     202202
2012    apr     201204
2023    may     202305
2019    dec     201912

Initially I thought to use case statement wherein I hardcode for each month, concatenate this with year column and finally parse the yearmon as int. Something like -

concat(year, case when month = 'jan' then '01'
...
... end)

Finally cast it to int

I tried below formats -

Select month("jan")

Select cast("jan" as date)

But nothing seem to have worked.

Is there a better way to cast this use case than hard coding the values in case statement?

cast your year, month and 01 as a date and then format/convert. so `select cast(concat_ws('-','01',table.month,table.year) as date) from table` — Nathan_Sav, Jul 07 '23 at 11:25
@Nathan_Sav Got error `INVALID_CAST_ARGUMENT: Value cannot be cast to date: 01-jan-2023` — djm, Jul 07 '23 at 11:41
https://stackoverflow.com/questions/8314310/convert-month-name-to-month-number-in-sql-server — Nishant Gupta, Jul 07 '23 at 11:43
@NishantGupta Already saw this thread. Doesn't work for my use case. — djm, Jul 07 '23 at 12:20
@NishantGupta the link is for SQL Server which has different SQL dialect compared to Presto/Trino on which Athena is based on. — Guru Stron, Jul 08 '23 at 16:07

score 1 · Accepted Answer · answered Jul 07 '23 at 22:43

You can try using date_parse and date_format. The following works in Trino on which the Athena is based on:

-- sample data
with dataset(year, month) as (
    values (2022, 'jan'),
    (2022, 'feb'),
    (2012, 'apr'),
    (2023, 'may'),
    (2019, 'jun')
)

-- query
select date_format(
             date_parse(cast(year as varchar) || '-' || month, '%Y-%b'),
             '%Y%m'
           )
from dataset;

Which produces the following output (TBH I was a bit surprised that it can handle lowercase month names):

_col0
202201
202202
201204
202305
201906

If this will not work for your actual data then using case-when is the approach you might want to take.

Thanks man. This worked just the way I needed. Cheers! – djm Jul 08 '23 at 06:49 — djm, Jul 08 '23 at 06:49

Is there a way to cast string to date to int in athena?

1 Answers1