CODEWITHSUNDEEP
sqlprestoamazon-athena
shantanuo
shantanuo

Reputation: 32286

cast and left functions in Athena

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

Upvotes: 1

Views: 10987

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

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.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522396

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.

Upvotes: 1

Related Questions