SQL: how to get sunday of current week

user_1317602 0
Viewed 40947

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!!

date sql-server-2008 sql
4 Answers
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.

user_107303440

The below doesn't work on a sunday with some DATEFIRST (languages):

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

Also, setting DATEFIRST is not always an option (functions can't use SET DATEFIRST)

Here's one that's universal:

  • It's geared for weeks starting on a Monday
  • Always returns the Sunday of the current week
  • Works regardless of the DATEFIRST setting
  • Does not change DATEFIRST so can be used in a function
    DECLARE @today DATE = getdate(); (or any other date);

    SELECT dateadd(day, ((15-@@datefirst) - datepart(dw, @today)) % 7, @today);
user_29184490
Related