Search code examples
amazon-web-servicesamazon-athenapresto

Calculate date and weekending date on Presto

Given day, month and year as integer columns in the table, calculate the date and weekending date from these values.

I tried following 

select date_parse(cast (2020 as varchar)||cast (03 as varchar)||cast (02 as varchar),'%Y%m%d')

returns an error saying "INVALID_FUNCTION_ARGUMENT: Invalid format: "202032" is too short"

Solution

  • The simplest way is to use format() + cast to date:

    presto> SELECT CAST(format('%d-%d-%d', 2020, 3, 31) AS date);
   _col0
------------
 2020-03-31

    Since Athena is still based on Presto .172, it doesn't have this function yet, so you can do the same without format:

    presto> SELECT CAST(CAST(2020 AS varchar) || '-' || CAST(3 AS varchar) || '-' || CAST(31 AS varchar) AS date);
   _col0
------------
 2020-03-31