CODEWITHSUNDEEP
sqlamazon-redshift
xx123
xx123

Reputation: 37

Is there a function to convert a string like YYYY-QQ to date in AWS Redshift?

I have a requirement for Redshift SQL where I need to convert a column like '2020-Q2' to start of the quarter date 2020-04-01. I tried few other posts but they don't seem to work. Read somewhere I can use below query but it doesn't really work:

select to_timestamp(to_date('2015-Q1', 'YYYY-MM-DD'), 'HH24:MI:SS');

I am planning to make a UDF but if there is an in-built function already, please share. Thanks!

Upvotes: 0

Views: 1007

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

The following works in Postgres and I think it should work in Redshift:

select to_date(left(yyyyqq, 4), 'YYYY') + (right(yyyyqq, 1)::int - 1) * interval '3 month'
from (select '2015-Q3' as yyyyqq) x

Upvotes: 2

Related Questions