Hive – Query to get Saturday as week start date for a given date

I have an requirement in hive to calculate Saturday as week start date for a given date in hive sql.

Eg)

Date                   week_start    
03-27-2021             03-27-2021    
03-28-2021             03-27-2021    
03-31-2021             03-27-2021    
04-07-2021             O4-03-2021    
04-09-2021.            04-03-2021

I tried using pmod and other date functions but not getting desired output. Any insight is much appreciated.

Answer

Hive offers next_day(), which can be adapted for this purpose. I think the logic you want is:

select date_add(next_day(date, 'SAT'), -7)

This is a little arcane. next_day() gets the next date after the argument date with a given day of the week. So, go to the next Saturday and then subtract 7 days for the start of the week.