Solved

Help in setting the first day of the week to Monday

Posted on 2016-11-07
6
33 Views
Last Modified: 2016-11-08
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
0
Comment
Question by:shah36
  • 3
  • 2
6 Comments
 
LVL 49

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 41877110
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 41877254
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
 

Author Comment

by:shah36
ID: 41878554
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 41878569
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
 

Author Comment

by:shah36
ID: 41878636
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
 

Author Closing Comment

by:shah36
ID: 41878639
Thank you guys. I had to set Datefirst to 1 but i am resetting to 7 after i execute the query.

regards,
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question