Solved

Help in setting the first day of the week to Monday

Posted on 2016-11-07
6
36 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 50

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 50

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

Technology Partners: 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!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

691 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