CODEWITHSUNDEEP
sqlamazon-redshift
thinksql
thinksql

Reputation: 39

How to change a column from string to date in Amazon Redshift?

Beginner here, have searched high and low for this answer but it doesn't work in Amazon Redshift's SQL.

I have a date column with YYYYMM format, etc 202101 = January 2021. I want to convert it to date format like 2021-01 or 01/2021, whichever is fine but date format. It is best if I can add a column and not replace the original column. But most answers talks about table, but I am using a view.

select CONVERT(DATA_PERIOD , '01')  FROM view 
LIMIT 10

SAMPLE DATA for DATA_PERIOD

DATA_PERIOD
202101
202101
202102
202102
202103

Would appreciate some help here, and if there is a way to know Redshift SQL syntax is closest to which language

Upvotes: 1

Views: 1254

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271141

You can convert it to a date using:

select to_date(data_period || '01', 'YYYYMMDD')

I would stop there. But you can then use to_char() to convert to any string representation you like.

Upvotes: 1

Related Questions