Thanks to a friend of mine:

Declare @lastSunday datetime

Declare @cDate datetime

Set @cDate = GETDATE()

Set @lastSunday = dateadd(d,-6,dateadd(d,(7 - datepart(dw,@cDate)),@cDate))

PRINT @lastSunday

quote:

---

DECLARE @LockDateBegin SMALLDATETIME

SELECT @LockDateBegin =

CASE

WHEN (DATEPART(dw, GETDATE() = 3

...

Anytime a programming dilama seems impossible to be solved, I remind myself "There has to be a way to solve it". And I found a way each time.