CODEWITHSUNDEEP
sql-servert-sql
del.ave
del.ave

Reputation: 1938

finding the start day (Monday) of the current week

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

Upvotes: 24

Views: 64286

Answers (5)

N. Haut
N. Haut

Reputation: 159

The most simple implementation

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

Upvotes: 12

Mirosław Dzieciątek
Mirosław Dzieciątek

Reputation: 11

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

Upvotes: 1

user2761633
user2761633

Reputation: 41

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

Upvotes: 4

Jay Horita
Jay Horita

Reputation: 101

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

Upvotes: 10

p.campbell
p.campbell

Reputation: 100557

Try using DATEFIRST to explicitly set the day of week to be regarded as the 'first'.

set DATEFIRST 1  --Monday
select DATEADD(DD, 1 - DATEPART(DW, @pInputDate), @pInputDate)

This will return the Monday of the week the InputDate falls in.

Upvotes: 36

Related Questions