?
Solved

Help in setting the first day of the week to Monday

Posted on 2016-11-07
6
Medium Priority
?
38 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 1000 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 1000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 51

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

771 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