Help in setting the first day of the week to Monday

Hi All,

I have this query
DECLARE @DateofWeek DATETIME = '20161120'
SET DATEFIRST 1
SELECT DATEADD(ww, DATEDIFF(ww,0,@DateofWeek), 0) AS WeekStartMonday

Open in new window


I want to start the week from Monday. It all works fine for Mon-Sat and date goes back to Monday but for Sunday the date is set to the coming monday (tomorrow).
How can i change its behavior so that for sunday instead of going back a day forward it goes back to the previous monday?

regards
Ali ShahSQL DeveloperAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Use a CASE to verify if is Sunday or not:
DECLARE @DateofWeek DATETIME = '20161120'
SET DATEFIRST 1
SELECT CASE DATEPART(ww,@DateofWeek)
	WHEN 7 THEN DATEADD(ww, DATEDIFF(ww,0,@DateofWeek), 0) 
	ELSE DATEADD(ww, DATEDIFF(ww,0,@DateofWeek-1), 0) 
	END AS WeekStartMonday

Open in new window

0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Don't change the DATEFIRST setting just for this!  You don't need to at all, and you risk serious issues by doing so.  [For example, if someone later decided to add a function call in the code for some reason, that code might not work correctly because it was expecting the default DATEFIRST setting.]

Instead, use the code below, which works under any/all date settings.

DECLARE @DateOfWeek datetime
SET @DateOfWeek = '20161120'

--back up to Monday (if current date is Monday, don't adjust)
SET @DateOfWeek = DATEADD(DAY, -DATEDIFF(DAY, 0, @DateOfWeek) % 7, @DateOfWeek)

SELECT @DateOfWeek AS WeekStartDate
0
 
Ali ShahSQL DeveloperAuthor Commented:
Thank you very much both of them work. However when i try it in my code and try to get the weeknumber for that week. The sunday goes returns the next weeknumber.

For example the week starting from 28/12/2015 and ending at 03/01/2016 returns three different week numbers 53,1,2
from 28/12 to 31/12 it returns 53,
from 01/01 ot 02/01 it returns 1
and 03/1 it returns 2

is it possible to set the behavior so that i get the one week number from monday to sunday?

regards,

Ali
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
from 28/12 to 31/12 it returns 53,
 from 01/01 ot 02/01 it returns 1
 and 03/1 it returns 2
I think is the correct behavior.
Last week of the year --> 53
1st week of the year --> 1
2nd week of the year --> 2

The scope is always the year.
0
 
Ali ShahSQL DeveloperAuthor Commented:
You are right but it was our requirement to start from 28/12/2015 and consider this as first week. So I hard coded it like

CASE Dates.WeekOfYearInteger
                          WHEN 53 THEN 1
                          ELSE DATEPART(WEEK, Dates.CalendarDate)
                        END AS WeekNumber

Open in new window

It works for now.

regards
0
 
Ali ShahSQL DeveloperAuthor Commented:
Thank you guys. I had to set Datefirst to 1 but i am resetting to 7 after i execute the query.

regards,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.