finding the start day (Monday) of the current week

user_173850 0
Viewed 41848

Looking for a SQL query/queries that would determine the start day (Monday) of the current week.

Example: If today is -> then the start of the week is

Sat Oct 09, 2010 -> Start of the week is Monday Oct 04, 2010
Sun Oct 10, 2010 -> Start of the week is Monday Oct 04, 2010
Mon Oct 11, 2010 -> Start of the week is Monday Oct 11, 2010
Tue Oct 12, 2010 -> Start of the week is Monday Oct 11, 2010

I have seen many "solutions" on Google and StackOverflow. The look something like:

SET @pInputDate = CONVERT(VARCHAR(10), @pInputDate, 111)
SELECT DATEADD(DD, 1 - DATEPART(DW, @pInputDate), @pInputDate)

This fails because: Sun Oct 10, 2010 -> start of week Monday Oct 11, 2010 (which is incorrect).

tsql sql-server
5 Answers

Building on top of p.campbell's solution, if you don't want to use or can't use "SET DATEFIRST 1", you can get around that by doing the following:

SELECT DATEADD(DD, 2 - DATEPART(DW, DATEADD(DD, -1, @pInputDate)), DATEADD(DD, -1, @pInputDate))
user_20490390

The most simple implementation

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) MondayOfCurrentWeek

user_67488420

You don't need to use DATEFIRST:

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)     -- Monday of current week
SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()) - 1, 0) -- Monday of last week
SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()) + 1, 0) -- Monday of next week

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) + 4 -- Friday of current week
user_27616330

You need to use DATEFIRST. Without it your code always assigns Sunday to "wrong" week:

    SELECT DATEADD(wk, DATEDIFF(wk,0,'2020-10-11'), 0) --> Sunday -> 2020-10-12
    SELECT DATEADD(wk, DATEDIFF(wk,0,'2020-10-12'), 0) --> Monday -> 2020-10-12
    SELECT DATEADD(wk, DATEDIFF(wk,0,'2020-10-18'), 0) --> Sunday -> 2020-10-19

In this case 2 factors needs to be taken care of:

  1. @@DATEFIRST value
  2. What day your weeks starts on (for me it is Monday)

E.g.: all below lines produce 2020-10-12 (Monday for week starting on Monday, 2020-10-12 and ending on Sunday, 2020-10-18) regardless of @@DATEFIRST value:

    SELECT DATEADD(DAY, -((@@DATEFIRST + 7 - (2 - DATEPART(WEEKDAY, '2020-10-12')) % 7) % 7), '2020-10-12')
    SELECT DATEADD(DAY, -((@@DATEFIRST + 7 - (2 - DATEPART(WEEKDAY, '2020-10-13')) % 7) % 7), '2020-10-13')
    SELECT DATEADD(DAY, -((@@DATEFIRST + 7 - (2 - DATEPART(WEEKDAY, '2020-10-14')) % 7) % 7), '2020-10-14')
    SELECT DATEADD(DAY, -((@@DATEFIRST + 7 - (2 - DATEPART(WEEKDAY, '2020-10-15')) % 7) % 7), '2020-10-15')
    SELECT DATEADD(DAY, -((@@DATEFIRST + 7 - (2 - DATEPART(WEEKDAY, '2020-10-16')) % 7) % 7), '2020-10-16')
    SELECT DATEADD(DAY, -((@@DATEFIRST + 7 - (2 - DATEPART(WEEKDAY, '2020-10-17')) % 7) % 7), '2020-10-17')
    SELECT DATEADD(DAY, -((@@DATEFIRST + 7 - (2 - DATEPART(WEEKDAY, '2020-10-18')) % 7) % 7), '2020-10-18')
user_144420660
Related