I need to convert a script written in hive-SQL to Redshift's SQL.
I am stuck with the following piece:
select date_format(date_add(date_sub(current_date,cast(from_unixtime(unix_timestamp(current_date, 'yyyy-MM-dd'),'u') as int)),7), 'dd MMM')
Your Hive query returns next Sunday, or current date if it is Sunday if I got it right. You can use date_part to check which is the day of week and next_day to get next Sunday. Something like this (not tested, do not have Redshift):
to_char(case when date_part(dow,current_date) = 0 then current_date
else next_day(current_date ,'Sunday')
end, 'DD MON')