SQL: how to get sunday of current week

Question

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

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

Any answer is welcomed!!

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

DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 6)

I know this post is old but this post comes at the top when searched through via Google.

Always good to be updating.