Converting hive Timestamp functions to Redshift syntax
Solution 1:
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')