Starting on Monday
-
Comments posted to this topic are about the item Starting on Monday
-
Actually, none of the answers are correct (and I didn't click an answer because of that... they're all incorrect).
The first answer is syntactically incorrect because DATEPART only takes two operands.
The second answer is incorrect because Collation has nothing to do with dates. Language, yep (it changes DATEFIRST). Not collation.
The fourth answer is incorrect because that would set DATEFIRST to Tuesday.
That leaves the 3rd answer of SET DATEFIRST 1, which correctly sets DATE FIRST to Monday. It doesn't do squat for weeks, though. In order to keep things like DATEDIFF deterministic, they had to make a bit of an exception when it came to the WK/WEEK/WW date part not to mention that DATEDIFF(wk) always assumes that the week starts on Sunday, again, because of that deterministic issue. The following code shows what a train-wreck that can create...
--===== Set Date First to Monday
SET DATEFIRST 1
;
--===== Start and End dates of the date range.
DECLARE @StartDR DATE = '27 Dec 2021'
,@EndDR DATE = '12 Jan 2022'
;
--===== Proof code
WITH cteDateRange AS
(
SELECT Date = DATEADD(dd,t.N,@StartDR)
FROM dbo.fnTally(0,DATEDIFF(dd,@StartDR,@EndDR))t
)
SELECT dr.Date
,DoW = DATENAME(dw,dr.Date)
,WK# = DATEPART(wk,dr.Date)
,Note = CASE
WHEN dr.Date = '01 Jan 2022'
THEN 'Week 1 of 2022 started on a Saturday and was only 2 days long!'
WHEN DATENAME(dw,dr.Date) = 'Monday'
THEN 'New week started on Monday'
ELSE ''
END
FROM cteDateRange dr
;Here are the results...
Ironically, the proof code above contains one of the ways to check if a given date is a Monday... Use DATE NAME(dw,SomeDT) to see if "Monday" is returned which, of course, doesn't work if the LANGUAGE isn't set to recognize that spelling of Monday. So, even that isn't "safe" in this Global Economy.
The following code contains two of the safe ways to determine if a given date is a Monday (regardless of the set language or DATEFIRST). Do note the comment next to SET DATEFIRST and give it a try.
--===== Set Date First to whatever you want.
SET DATEFIRST 7 --Just to emphasize the point. Change it to any value of 1 thru 7.
;
--===== Start and End dates of the date range.
DECLARE @StartDR DATE = '27 Dec 2021'
,@EndDR DATE = '12 Jan 2022'
;
--===== Proof code
WITH cteDateRange AS
(
SELECT Date = DATEADD(dd,t.N,@StartDR)
FROM dbo.fnTally(0,DATEDIFF(dd,@StartDR,@EndDR))t
)
SELECT dr.Date
,DoW = DATENAME(dw,dr.Date)
,IsMonday = IIF((DATEPART(dw, dr.Date) + @@DATEFIRST -1) % 7 = 1, 1, 0)
,IsoDW = (DATEPART(dw, dr.Date) + @@DATEFIRST -2) % 7 + 1
,IsoWK = DATEPART(isowk,dr.Date)
,WK# = DATEPART(wk,dr.Date) --Still incorrect even for Sunday.
,Note = 'The WK/WW/WEEK datepart is still the wrong thing to use.'
FROM cteDateRange dr
;And that returns the following...
Why MS didn't create an IsoDW function, I have no idea. Why they didn't properly include the YEAR in the IsoWK function, I also never know. And why they didn't include a function to convert a proper IsoWK to a valid week-starting date, I'll also never know. Why the ISO committee decided the DoW numbering should start at 1 instead of 0 is also beyond me. I guess they figured they were going to blow enough minds without having to say that the first day of the week is day 0. 😀
So, the bottom line is that, by itself, using SET DATEFIRST 1 is also an incorrect answer.
With that, I also say that people should just pretend that the WK/WW/WEEK datepart simply doesn't exist. The only place where it actually works correctly (that I know of) is with DATEADD() because it adds duration instead of counting boundaries like DATEDIFF() does.
One final note... be aware that someone logging in with a different default language can also affect DATEFIRST.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
As a bit of a sidebar... I have a visceral hate for DATEFIRST() and @@DATFIRST(). It just bugs me.
With that, and even though it's a tiny bit slower, I use the following method to come up with the day of the week. Since it's for Mondays, I still call it "IsoDW".
;--===== Set Date First to whatever you want.
SET DATEFIRST 3 --Just to emphasize the point. Change it to any value of 1 thru 7.
;
--===== Start and End dates of the date range.
DECLARE @StartDR DATE = '27 Dec 2021'
,@EndDR DATE = '12 Jan 2022'
;
--===== Proof code
WITH cteDateRange AS
(
SELECT Date = DATEADD(dd,t.N,@StartDR)
FROM dbo.fnTally(0,DATEDIFF(dd,@StartDR,@EndDR))t
)
SELECT dr.Date
,DoW = DATENAME(dw,dr.Date)
,IsoDW = DATEDIFF(dd,'1753',dr.Date)%7+1 --'1753' = 17530101, which is a Monday.
,IsoDwAlt = DATEDIFF(dd,-53690,dr.Date)%7+1 ---53690 is the "Date Serial Number for 17530101"
FROM cteDateRange dr
;Back before the advent of the newer temporal datatypes, the alternate method of using the "Date Serial Number for 17530101' worked just fine for DATETIME. For simplicity, I also used a date serial number of 0, which equates to 19000101, which is also a Monday. Date serial numbers frequently don't work with the newer temporal datatypes (only 0 of a blank work and only in some cases).
Here's the results from that code and it doesn't matter what you set DATEFIRST to...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Wow, as usual, thanks Jeff for the comprehensive breakdown!
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html -
Thank you for the very kind feedback, WebRunner. Coming from such a long time regular as you, it means a lot. Thanks again.
Looking back at that, it might make a good short article. Maybe I should polish up the gussets on it and submit it as an article. It might get me back into writing articles.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply