SQL: how to get the sunday of the current week

Question

I want to get the last day (Sunday) of the current week with any timestamp. I tried following the script, but it returns Saturday as the last day, not Sunday, as I expected.

Select DATEADD(DAY , 7-DATEPART(WEEKDAY,GETDATE()),GETDATE()) AS 'Last Day Of Week' 

Any answer is welcome!

Answer 1

It will work if you change the standard DATEFIRST from Sunday (7) to Monday (1):

 SET DATEFIRST 1 Select DATEADD(DAY , 7-DATEPART(WEEKDAY,GETDATE()),GETDATE()) AS 'Last Day Of Week' 

Answer 2

Prefix your command with set datefirst 1