cast and left functions in Athena

Question

cast and convert function works as expected in Athena:

SELECT code_2 as mydate,  cast( code_2 as varchar) from some_table

but how do I extract 8 leftmost characters? This throws an error:

SELECT code_2 as mydate,  left(cast( code_2 as varchar),8) as date from some_table

Here is the error:

extraneous input 'left' expecting

just FYI, right and left are not supported in Presto. use lpad, rpad, substr instead. `lpad(code_2,8,' ')` is the equivalent of `left(code_2,8)` https://prestodb.github.io/docs/current/functions/string.html — Ilya P, Sep 23 '19 at 20:45

score 1 · Accepted Answer · answered Sep 23 '19 at 05:15

1

Try casting directly to VARCHAR(8):

SELECT
    code_2 AS mydate,
    CAST(code_2 AS VARCHAR(8))
FROM some_table;

I have never used Athena, but the documentation implies that this should work. This trick works on Oracle, SQL Server, Postgres, and MySQL.

answered Sep 23 '19 at 05:15

Tim Biegeleisen

502,043
27
286
360

score 1 · Answer 2 · answered Sep 23 '19 at 11:54

If code_2 is a string, then use substr():

select code_2 as mydate, substr(code_2, 1, 8) as date
from some_table;

If code_2 is a date, then use the appropriate date function:

select code_2 as mydate,
       date_format(code_2, '%Y-%m-%d') as date
from some_table;

Use the appropriate function for the data type. Do not convert dates to strings when there are built-in functions that do exactly what you want and give you more control.

cast and left functions in Athena

2 Answers2