CODEWITHSUNDEEP
sqlamazon-redshiftamazon-athenaprestotrino
Sam Gilbert
Sam Gilbert

Reputation: 1702

How to convert a string in format YYYYMMDD to date without string manipulations in a consistent way in AWS redshift and AWS athena

I'm looking to convert a string into a date in a consistent way in both AWS redshift and AWS athena without string manipulation.

The string that I need to convert into a date is in the format YYYYMMDD.

(I know the format YYYY-MM-DD would be easier to work with, however, I am unable to change the source format).

The only solution that I have currently found that works in both is:

SELECT CAST(SUBSTRING('20220101',1,4) || '-' ||SUBSTRING('20220101',5,2) || '-' ||SUBSTRING('20220101',7,2) AS DATE)

I.e. manipulating the string into a more useable format from YYYYMMDD to YYYY-MM-DD, then casting as a date.

However, this feels overcomplicated, and I'm hoping there is a conversion to date function that I have missed.

I have tried a few things, but no luck so far.

For example, in redshift, but neither work in Athena:

SELECT '20220101'::DATE

or

SELECT CAST('20220101' AS DATE)

And the closest I can get to in athena is as follows:

SELECT CAST(date_parse('20220101','%Y%m%d') AS DATE)

This isn't the solution though as date_parse isn't available in redshift.

Upvotes: 2

Views: 5561

Answers (1)

Guru Stron
Guru Stron

Reputation: 142823

Athena and Redshift have different SQL flavors (Redshift is based on PostgreSQL and Athena is based on Presto/Trino).

For Redshift use the TO_DATE function:

select to_date('20010630', 'YYYYMMDD');

Upvotes: 2

Related Questions