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

So you want week to be Monday -> Sunday? Default in SQL Server is Sunday -> Saturday. — Aaron Bertrand, Jul 20 '12 at 13:58
Also refer this http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspx — Madhivanan, Jul 20 '12 at 14:04

score 20 · Accepted Answer · answered Jul 20 '12 at 13:59

20

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'

answered Jul 20 '12 at 13:59

Aaron Bertrand

272,866
37
466
490

score 2 · Answer 2 · answered Jul 30 '19 at 17:50

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.

answered Jul 30 '19 at 17:50

Jona

327
4
19

score 1 · Answer 3 · edited Oct 24 '19 at 16:55

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);

score 0 · Answer 4 · answered Jul 20 '12 at 14:00

0

Prefix your command with set datefirst 1

answered Jul 20 '12 at 14:00

podiluska

50,950
7
98
104

SQL: how to get sunday of current week

4 Answers4