Solved

Help in setting the first day of the week to Monday

Posted on 2016-11-07
6
29 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 47

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

786 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