Hmm, not changing the DATEFIRST here - but I seem to get Sunday 😀

Declare @currentDate datetime

,@sundayDate datetime;

Set @currentDate = getdate()

Set @sundayDate = dateadd(week, datediff(week, -1, @currentDate), -1);

Select @sundayDate, datename(weekday, @sundayDate);

Set @currentDate = dateadd(day, -5, getdate());

Set @sundayDate = dateadd(week, datediff(week, -1, @currentDate), -1);

Select @sundayDate, datename(weekday, @sundayDate);

Now, if I want every Tuesday we change the seed date:

Declare @currentDate datetime

,@tuesdayDate datetime

,@seedDate datetime;

Set @seedDate = '20100105'; -- First Tuesday of this year

Set @currentDate = getdate()

Set @tuesdayDate = dateadd(week, datediff(week, @seedDate, @currentDate), @seedDate);

Select @tuesdayDate, datename(weekday, @tuesdayDate);

Set @currentDate = dateadd(day, 10, getdate());

Set @tuesdayDate = dateadd(week, datediff(week, @seedDate, @currentDate), @seedDate);

Select @tuesdayDate, datename(weekday, @tuesdayDate);

Should do it...