CODEWITHSUNDEEP
sqldate
whatwhatwhat
whatwhatwhat

Reputation: 2276

SQL: Returning Monday of the week not working for Sundays

I want to input a date and have it return the Monday of that week, but at my company the week starts on Monday and ends on Sunday. I tried using SET DATEFIRST 1 to make Monday the first day of the week, but this didn't help me with Sundays.

code:

DECLARE @proddate datetime = '6/12/2016'
SET DATEFIRST 1; SELECT DATEADD(wk, DATEDIFF(wk,0,@proddate), 0) MondayOfWeek

returns:

6/9/2016 -> 6/6/2016
6/12/2016 -> 6/13/2016 (should be 6/6/2016)

Upvotes: 3

Views: 257

Answers (1)

Mack
Mack

Reputation: 2552

Try this:

SELECT DATEADD(DAY, 1-DATEPART(WEEKDAY, @proddate), @proddate)

Upvotes: 1

Related Questions