tl:dr
See the attached dbo.WeekDates function.
You don’t know what you’re missing, though. 😉
Prologue
Thanks to some really clever temporal functions that are built into SQL Server, working with dates and times in SQL Server is pretty easy.
The exception to that observation is when we have to work with WEEKs. No other date-part in SQL Server always needs to start on the same day of the week. That’s actually the source of several problems when it comes to calculating things like the Start of Week Date, the End of Week Date, and a whole lot more.
Let's first take a look at some of those problems so we know what to avoid, if we can.
The “Week Leap Years” Problem
Working with WEEKs is especially painful because the 365 or 366 days in a year are NOT evenly divisible by 7, the remainder of which must eventually be accounted for by including the occasional 53 week “Leap Year”, which doesn’t match the normal solar leap years. Even the ISOWEEK date-part can’t get around this problem
The “Difference” Problem
There are some super easy “Beginning of Period” calculations to be had with all the other date-parts that work using DATEDIFF(). Not so with the WEEK (wk) date-part. In order to keep DATEDIFF(wk) deterministic, DATEDIFF(wk) only uses Sunday as the first day of the week regardless of the DATEFIRST setting. In other words, it ignores DATEFIRST. That means that none of that other clever periodic code that uses DATEDIFF() can work with the WEEK (wk) date-part UNLESS you want Sunday to be the first day of the week.
You can read more about the issue at the following link: https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?#remarks
Here’s the quote of the pertinent information from the article at the link above (the emphasis is mine)…
Specifying SET DATEFIRST has no effect on DATEDIFF. DATEDIFF always uses Sunday as the first day of the week to ensure the function operates in a deterministic way.
DATEDIFF_BIG() has the same problem and you can find that in the article at the following link: https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-big-transact-sql#remarks
As it turns out, DATEDIFF(ISOWK) will also not work for the same reason. They don’t bother to tell you the reason… they simply don’t list ISOWK as one of the supported date-types for use with DATEDIFF in the documentation.
The “First Week” Problem
Actually, even working with Sundays is an issue with DATEPART(wk). If you read the documentation at the following URL: https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql#week-and-weekday-datepart-arguments
… you'll find the following statements…
January 1 of any year defines the starting number for the week datepart. For example:
DATEPART (wk, 'Jan 1, xxxx') = 1
where xxxx is any year.
In plain English, that means that no matter what day of the week it is, the week number will ALWAYS be reset to “1” on the 1st of January, no matter the year.
That’s a real issue. In most cases when working with weekly periods, the weeks are supposed to all start on the same day. They’re also supposed to end on the same day but the day before any 1st of January, which is the last day of the year, can also be any day of the week.
Again, ISOWEEK doesn’t have this problem. It always starts on a Monday and always ends on a Sunday, even for the 1st or last weeks of the year. Of course, that also means that the ISOWEEK doesn’t ever match the Calendar year. It can have days from the previous year and days from the next year included in the “ISO Year”.
The “Week Number” Problem
The first week of the ISOWEEK is identified as the first week that contains at least 4 days of the new year. That can be interpreted as the week containing the first Thursday of the year , or the week that contains the 4th of January, or the week that contains the 4th day of the year, or the year of the 4th day of the week (remember that it’s also the “Middle of Week Date” or MoW for later).
And, even though the ISOWEEK date-part figures all that auto-magically, there is no ISOYEAR date-part and there’s no such thing in the ISO specs as an “ISO Month”. This leads people to some seriously complicated formulas that really aren’t necessary.
The “Key” to Fixing ALL those Problems
If you need to work with weeks that always start AND end on the same day, regardless of which day of the week that needs to be, then the key to fixing all of those problems is to determine the date of the first day of the week for any given date.
In fact, a fairly common question on forums and the topic of many blog articles is the question of…
"How do I get the first day of the week?"
A very common and effective answer to that question can be found in the following snippet of code…
SELECT DATEADD(dd,1-DATEPART(dw,SomeDate),SomeDate);
…and it’s nasty fast!
It does have one possible issue though… Since it uses the “dw” (day of week) date-part, it’s dependent on which day of the week DATEFIRST is set to and that may not always be what you need for the first day of the week. You might even have code that has to work for many different companies that have different starting days of the week even amongst themselves.
Yes, there IS code to work around that issue and some of the other issues. Some of it includes changing DATEFIRST at the session level (does NOT affect other sessions) thanks to the operand of DATEFIRST allowing a variable instead of just a literal. (You can’t set it in a function though)
You can search for and find such code and use it as the well-head to do the other calculations from. Unfortunately, and unlike the example code above, a lot of such code is based on RBAR1 in one way or another and that can cause several different performance issues including total suppression of parallel processing and more especially since that will require a Scalar Function. For more on that, see the following Brent Ozar articles. He has even more on the subject but these will get you started on the subject of why Scalar Functions are so bad for all of your code:
- https://www.brentozar.com/archive/2019/05/user-defined-scalar-functions-suck-even-when-they-dont-access-data/
- https://www.brentozar.com/archive/2016/01/another-reason-why-scalar-functions-in-computed-columns-is-a-bad-idea/
Purpose of this Article
To summarize what we know, the ISOWEEK does some pretty cool things...
- Every week starts with the same day as every other week.
- Every week ends with the same day as every other week.
- Every week always contains exactly 7 days.
- It’s not affected by DATEFIRST. No surprises.
It also has some disadvantages…
- It’s not affected by DATEFIRST. It always starts on Monday. You can’t set it to some other day even if you need to.
- The DATEPART(isowk) doesn’t offer a clue as to which year, quarter, or month the week is a part of. It doesn’t come close to the ISO standard in that area.
- The first day of the calendar year is on the first day of the first week of the year only 1 time in 7.
- The last day of the of the calendar year in on the last day of the last week of the year only 1 time in 7.
This article is going to demonstrate several “simplicities” where we can treat any day of the week like ISOWEEK treats Mondays and without most of the disadvantages of using ISOWEEK. Disadvantages 3 and 4 are insurmountable, in this case. It’s just the nature of using “whole weeks”.
That’s not the only purpose of the article, though. This article is also a serious deep dive into the code with a ton of examples and explanations of how the code actually works and some of the decisions made to keep the code as simple and fast as possible even though it does a great deal. You’ll also learn about using “Table Value Constructors” to “DRY” your code out.
In plain English, this article contains a shedload of ancillary information that can be used in other related and sometimes unrelated areas of your coding.
In the end, you’ll walk away with a fully documented function with a huge amount of understanding of what the incredibly simple code is actually doing and why it can be so very simple.
Overview of the Upcoming Code
As previously stated, some pretty complicated code has been written to do the things we’re going to do. It just doesn’t need to be that complicated, though.
Basically, we going to use some really simple Integer Math to do the following and you’ll be amazed at how simple the code actually is.
- Calculate the Offset --This is simply the number of days the “given date” is from the first day of the week. The remarkable thing is, we don’t know the first day of the week yet. In fact, we’re going to use this Offset value to figure out what the date of the first day of the week is. You’ll be amazed at how simple this code is.
- Calculate the DW# (Day of Week) - This is a pretty elusive number in a whole lot of calculations because the value of DATEFIRST may be different than the day of the week we want to use as the first day of the week. This code is incredibly simple thanks to knowing the Offset.
- Calculate the SoW (Start of Week) – This is the actual date of the first day of the week that our “given date” is a part of. Again, this code is incredibly simple thanks to knowing the Offset.
- Other Dates – Once we know the SoW (Start of Week) date, the temporal world is our oyster. Here are some of the things that we’ll be able to calculate through simple addition in the form DATEADD().
- MoW (Middle of Week) – This is a very special date that a huge number of other super simple calculations rely on.
- EoW (End of Week) – The last day of the week.
- SnW (Start of next Week) – The first day of the next week, which makes for some code that will work universally whether or not our dates have times or not using the Closed/Open method for criteria.
- Date-Parts for the Week - Because we know the special MoW (Middle of Week) date, there’s actually no math involved with these returns AND they’re guaranteed to work properly with the edge case of the week that contains the last days of one year and the first days of the next year.
- YY# - The year the week belongs to.
- QQ# - The quarter the week belongs to.
- MM# - The month the week belongs to.
- WK# - The week number of the year. This one is the most complicated formulas because it requires a temporal function, an addition, and a division… so NOT so complicated, really. You’ll be absolutely amazed at how easy this is done without loops, LEADs, LAGs, Tally/Nums functions, etc.
- As a final step, we’ll also convert the code to take a parameter so that we can either make the code automatically follow DATEFIRST or make it select a specific day of the week regardless of what DATEFIRST is set to.
- Of course, this will be set into a very high performance iTVF (inline Table Valued Function) for super easy inclusion in other code.
- We'll demo not only how to use the function, but how to use it to either augment your calendar table or build a separate one. In the process, you also find some simple code that will produce REAL ISO WEEKs in the form of yyyyWww indirectly thanks to knowing the MoW because "The MoW knows" everything.
Calculating the “Week Date” Columns
Ladies and gentlemen, please don your water-cooled helmets and start your engines. Explaining simplicity is sometimes a complex task.
Finding the “Offset”
First, we need to find the number of days that the “given date” is away from the first of the week. This is the key to everything else either directly or indirectly.
I know… “umm… WHAT??? Now wait a minute… We don’t know the date of the first of the week. How are we going to do that”?
Not to worry. It’s actually quite easy. Just for ease of explanation, let’s assume that we want Monday to be the first day of the week and that the “given date” occurs on a Friday.
Discussion
Now, ask yourself... What DO we know about the first day of the week for the given Friday?
I know… “Well, duh, Jeff, it has to be a Monday”.
Absolutely correct. We just don’t know WHICH Monday it is. The other thing we know is that the “given date” is on a Friday but we can’t necessarily use the “dw” (Day of Week) date-part to tell us that because it relies on the DATEFIRST setting and that might not be set to “1” for Monday like we want the week to start with.
What we do know is, no matter the “given date” and no matter WHICH Monday it is, if we compare the two dates and throw away the whole weeks between them and then count the days that remain, it will tell us how far away our “given date” is from the Monday we want, which is our week-starting date.
Sooooo… let’s use a date that we DO know is a Monday. And rather than limit ourselves to days after the year 1900 like a lot of methods do, let’s use the earliest possible date available in SQL Server (as of the 2008 version of SQL Server) and that’s 01 January 0001. Very conveniently, it also happens to be a Monday.
So, here’s the example. The date we’re given is 17 June 2022 and that’s a Friday. The start of that week is Monday, 13 June 2022, but we don’t know that, yet. That’s the date we’re trying to figure out.
We need to pick a “Monday” to reference. Again, we’ll use Monday, 01 January 0001 (the earliest date possible in SQL Server), as our “Monday” reference date. Then, we’ll take the difference in days between that “reference date” and the “given date”, throw away the whole weeks, and the remaining days will tell us how far away our 17 June 2022 date is from the Monday of the week that our "given date" is in. After that, simple subtraction (again, using DATEADD()) will tell us the date of the first of the week.
That sounds a bit complicated but it’s not. Thanks to some “Integer Math”, it’s really simple. Let’s see that…
Exploring the Code
If we execute the following snippet of code, we’ll count the number of whole weeks between Monday, 01 January 0001 and our "given date" (@SomeDate). It does this by calculating the number of DAYS between the two dates and then dividing that number by 7. Since it's all Integer Math, only the integer part of the quotient of the division is returned and any remainder is simply discarded.
--===== Number of whole weeks between dates using -- Integer Math, which throws away the remaining days. -- Not what we want. DECLARE @SomeDate DATE = '17 Jun 2022'; --The "Given Date" SELECT WholeWeeks = DATEDIFF(dd,'0001-01-01',@SomeDate)/7; --<----<<<
(Note that I use the “--<----<<<” symbolic arrow to help point out what changed in similar code or what's important.)
That’s NOT what we want, though. We want to throw those whole weeks away and only keep the remaining number of days as an integer.
We could do some complicated math to do that but, fortunately, we can use Modulo to easily do that for us. Modulo returns the REMAINDER from a division and since DATEDIFF() returns an Integer and we’re dividing by an Integer, our answer will also be in the form of an Integer. That number will be the number of days our “given date” is from the Monday we actually want. So… our formula now becomes the following where the “%” sign is the Modulo operator (the integer remainder of division)…
--===== Number of days not part of the whole-week count. -- It's the REMAINDER and that’s our OFFSET! -- We replaced "/" with "%", which is the operator -- for MODULO or MODLULUS DECLARE @SomeDate DATE = '17 Jun 2022'; --The "Given Date" SELECT Offset = DATEDIFF(dd,'0001-01-01',@SomeDate)%7; --<----<<<
(Again, I use the “--<----<<<” symbolic arrow to help point out what changed in similar code or what's important.)
That returns a “4”, which is how many days our “given date” (@SomeDate) is away from the Monday at the first of the week.
Finding the Day of the Week (DW#)
One of the problems we had in trying to find the first day of the week was the fact that the DW date-part (Day of the Week) we’re trying to use might be different from what the DW date-part actually uses because DATEFIRST might be different for the starting day of weeks that we want.
Let's fix that.
We just found the number of days from the start date that the “given date” is even though we don’t know the actual start date of the week (yet). We called that the Offset.
We also know start date of the week is going to be a Monday and we do know that we’re 4 days away from that date. That means the "given date" is the 5th day of the week. All we need to do is add “1” to the Offset value we just found and we’ll have the correct value for DW#, the day of the week, for our “given date”.
Exploring the Code
Since we need to display the Offset and the DW# (Day of Week for the “given date”) and DW# also uses the Offset value, we’d have to use the Offset formula twice and that would be repeating ourselves. We can “DRY” (Don’t Repeat Yourself) out the code by calculating the value for the Offset using 1 copy of the formula. From there, we can use that value as often as we need to in the SELECT list including the simple calculation for DW#. We can’t use a variable for this because we eventually need it all as a “single query” to be included in a high performance iTVF (inline Table Valued Function).
Instead, we’ll do that in the FROM Clause that contains the result of the formula as a “Table Value Constructor”, even though it’s only going to return just one value. Think of it like using the “Memory” key on a calculator and giving the value a name.
--===== Find the DW (Day of the week) for the "given date". -- This is easy... Just add "1" to the Offset. -- We use a CROSS APPLY to "DRY" our code out a bit. DECLARE @SomeDate DATE = '17 Jun 2022'; --The "Given Date" SELECT Offset = v1.Offset ,DW# = v1.Offset+1 --<----<<< FROM (VALUES(DATEDIFF(dd,'0001-01-01',@SomeDate)%7)) v1(Offset) --<----<<< ;
That returns the following:
For more information on “Table Value Constructors”, please see the article at the following link: https://docs.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql
Finding the Start Date of the WEEK (SoW)
All that remains to solve for the first day of the week is to do is a little subtraction. We know the “given date”. We calculated the Offset in days that that starting day of the week is from the “given date”. We just need to subtract the OffSet from the “given date” and we’ll know the date for the SoW (Start of the Week).
As was stated in the “Overview of the Upcoming Code” section above, the SoW is also going to be used in a few other places. Let’s keep that “DRY”, as well, by doing the SoW calculation in a CROSS APPLY, which can also contain a “Table Value Constructor”.
--===== Find the SoW (Start of Week) Date.
-- This is easy... Just subtract the OFFSET from the "Given Date".
-- We use DATEADD() to add a negative OFFSET.
-- We use CROSS APPLY to "DRY" that bit of code, as well.
DECLARE @SomeDate DATE = '17 Jun 2022';--The "Given Date"
SELECT Offset = v1.Offset
,DW# = v1.Offset+1
,SoW = v2.SoW --<----<<<
FROM (VALUES(DATEDIFF(dd,'0001-01-01',@SomeDate)%7)) v1(Offset)
CROSS APPLY (VALUES(DATEADD(dd,-Offset,@SomeDate))) v2(SoW) --<----<<<
;
;And that returns the date that the calendar says is correct for the first day of the week that the "given date" is in… Monday, 13 June 2022.
Ok… so that’s a wee bit more complicated than the original formula way back at the beginning of this article but… it’s going to be SO WORTH IT! 😊
Finding other Days of the Week
It’s now possible to easily calculate some other important days of the week.
Discussion for “End of the WEEK” Date (EoW)
Right after people ask the question of …
"How do I get the first day of the week?"
… the normal follow-up question is…
"How do I get the LAST day of the week?"
This is actually the wrong question to ask because the date for the last day of the week is a “whole date” with no time. If the data that we need to work with as a result of all this includes times with the dates, then using the “LAST day of the week” might allow us to actually miss a huge number of entries on that date because they have a time after midnight of that “Whole LAST Date”.
We’ll address that in a minute but the BIG reason why we need to know the “LAST day of the week” is because of Accounting jargon and the related requirement. I have yet to hear an Account say something like “For week starting on…”. Instead, they always say “For week ENDING on…”.
And so, the “LAST day of the Week” is still an important date even though it’s a huge mistake to use it for criteria. It should ONLY be used as a “label” for a week, if it’s going to be used at all.
What to use for computational criteria instead?
Discussion for the “Start Date of NEXT Week” (SnW)
Like we talked about above, the “LAST day of the Week” is calculated as a “Whole-Date” and it inherently means that the date is valid for only one time of the day… midnight at the start of the day. If you make the MISTAKE of using criteria in your WHERE clause like (which is exactly the code created behind the scenes when BETWEEN is used)…
WHERE SomeDate >= SoW AND SomeDate <= EoW -- SoW = Start of Week, EoW = End of Week
… then you missed virtually the whole last day of the week in your criteria. That, by the way is known as a "Closed/Closed Criteria". It means that both dates are “Inclusive” but, as we just said, including only the first instant of the EoW (End of Week) date can miss virtually the whole day.
As a note of interest, the term “Closed” comes from the fact that a symbol on a “Gannt Chart” used to indicate that a date on the chart is "Inclusive", the symbol “filled or closed in”. Think of it as “up to and including”.
The term “Open” is used to mean the date is "Exclusive" and the symbol is “not-filled-in or open” to indicate an “exclusive” date. Think of it as “up to and NOT including”.
To avoid the possible missed data, it actually IS a Best Practice to use “Closed/Open” criteria. In other words, it should inclusively start at the start date and go up to and NOT including the next start date. Here’s an example…
WHERE SomeDate >= SoW AND SomeDate < SnW -- SoW = Start of Week, SnW = End of NEXT Week
Do notice the “<” relational operator in the formula, which replaced the "<=" relational operator.
Exploring the Code
So… We know the SoW (Start of Week) date and it's a given that weeks are always 7 days long.
To calculate the EoW (End of Week) date, we just need to add 6 days to the SoW. Likewise, to calculate the SnW (Start of NEXT Week) date, we just need to add 7 days to the SoW.
Because we already “DRY”ed out the SoW value, this is a cake walk. Here’s the code.
--===== Find the EoW (Emd of Week) Date and the SnW (Start of NEXT Week) Date. -- This is easy... -- Just add 6 to the SoW to get the Eow. -- Just add 7 to the Sow to get the SnW. DECLARE @SomeDate DATE = '17 Jun 2022'; --The "Given Date" SELECT Offset = v1.Offset ,DW# = v1.Offset+1 ,SoW = v2.SoW ,EoW = DATEADD(dd,6,v2.SoW) --<----<<< ,SnW = DATEADD(dd,7,v2.SoW) --<----<<< FROM (VALUES(DATEDIFF(dd,'0001-01-01',@SomeDate)%7)) v1(Offset) CROSS APPLY (VALUES(DATEADD(dd,-Offset,@SomeDate))) v2(SoW) ;
Here are the results from that code:
Like I said, because we “DRY”ed out the code for the value of SoW, this was super easy.
The Critical “Middle of the WEEK” Date (MoW)
The WHAT??? Why would we ever need to calculate such a thing?
We’ve now got code to calculate the “boundary” dates for our weeks. That’s great but we don’t actually know what year, quarter, or month the weeks actually belong to. Because of those edge-case weeks near the start of every year that have days from both the previous year and the current year, we need to pick the year, quarter, or month that has at least 4 days in the week and then we’ll assign those values as the year, quarter, or month for the entire week.
It would also be nice if we could assign a “Week Number of the Year” to each week, as well. We'll get to that in a bit but let's just worry about the assigning the correct Year, Quarter, and Month to the week for now.
We already know the SoW. Because dates always work from “left to right” for day, it’s a given that the 4th day of the week identifies the year, quarter, and month that has the majority of days for the edge-case weeks as well as the “full” weeks.
The 4th day of the week is squarely in the middle of the week. Hence, the name of “Middle of the Week” Date, which we’ll abbreviate to MoW. Ironically, it’s going to help us “MoW down” a whole bunch of formulas without any additional math once we know the MoW date.
Exploring the code
We're going to assign the proper Year (YY#), Quarter (QQ#), and Month (MM#) to the week here. Again, we'll get to the WK# (Week of the Year) in a bit.
Once again, we’re going to use this new MoW (Middle of Week) date many times and so we’re going to calculate it in a 3rd “Table Value Constructor” so that we can reuse its value many times. It’s also going to use the SoW that we created in the second “Table Value Constructor” and so that's another good reason to have created the SoW as the 2nd "Table Value Constructor" for reuse of its value.
Here's the code to calculate the MoW date:
--===== Calculating the MoW (Middle of Week) Date. -- This is easy... -- Just add 3 to the SoW to get the MoW. -- Remember that the SoW is the 1st day of the week and so -- we only need to add 3 to it to get the 4th day of the week, -- which is what the MoW is defined as. DECLARE @SomeDate DATE = '17 Jun 2022'; --The "Given Date" SELECT Offset = v1.Offset ,DW# = v1.Offset+1 ,SoW = v2.SoW ,EoW = DATEADD(dd,6,v2.SoW) ,SnW = DATEADD(dd,7,v2.SoW) ,MoW = v3.MoW --<----<<< FROM (VALUES(DATEDIFF(dd,'0001-01-01',@SomeDate)%7)) v1(Offset) CROSS APPLY (VALUES(DATEADD(dd,-Offset,@SomeDate))) v2(SoW) CROSS APPLY (VALUES(DATEADD(dd,3,v2.SoW))) v3(MoW) --<----<<< ;
Here are the results from that code:
Identifying the Periods for the Week (YY#, QQ#, MM#)
Now to identify which Year, Quarter, and Month the week belongs to. Time to MoW some problems down. 😊
If we look at what appears to be the first week January of the year 2015 according to the Windows Calendar when it’s set up for weeks to start on Monday, we can see that the “first week” (Red box) has days from both December 2014 and January 2015.
The Yellow box identifies what we now know as the “MoW” or “Middle of the Week” date. If you look at the Red box, you can clearly see that the 4 dates for January 2015 outnumber the more dim 3 days of December 2014. According to the ISO rules, this week has more days in 2015 than what are in 2014 and so the week belongs to the Year 2015. The 1st of January (the MoW, in this case) also clearly belongs to January 2015. That also means that it’s a part of the 1st Quarter of 2015.
The MoW (01 January 2015, in this case) is “magic”. It "knows" all of that. That means that we can simply extract all of that just by using the DATEPART() function for the Year, Quarter, and Month without any mathematical calculations.
So what happens for that same week if we want weeks to start on Sundays rather than Mondays? What kind of gyrations are we going to have to go through when that happens?
The answer is... NONE. We don’t have to change a thing because we already calculated the MoW and “the MoW knows”. 😊 Have a look at the Windows Calendar when Sunday is the first day of the week.
Again, we don’t have to count any days or change any calculations. The MoW knows! It’s always based on the 4th day of the week no matter what the starting day of the week is. In this case, the MoW for the week is in the last week of 2014 and using DATEPART() on the MoW would return 2014 for the Year, December for the Month, and 4 as the Quarter.
Exploring the Code
This is super simple… We already calculated the MoW (Middle of the Week) date. We just need to use DATEPART() to extract the Year, Quarter, and Month period assignments for the week. And, YES, this also works for ISO because it’s based on the same rules that ISO uses!
We’re going to change the “given date” in the code to Friday, 02 January 2015, just to demonstrate. Here’s the code for a Monday week start.
And, remember, the “Reference DATE” of '0001-01-01' is a MONDAY!
--===== Find the Year, Quarter, and Month for the week
-- with a MONDAY start date.
-- "The MoW Knows!"
-- Remember, '0001-01-01' is a MONDAY!
DECLARE @SomeDate DATE = '02 Jan 2015'; --The "Given Date" --<----<<<
SELECT Offset = v1.Offset
,DW# = v1.Offset+1
,SoW = v2.SoW
,EoW = DATEADD(dd,6,v2.SoW)
,SnW = DATEADD(dd,7,v2.SoW)
,MoW = v3.MoW
,YY# = DATEPART(yy,v3.MoW) --<----<<<
,QQ# = DATEPART(qq,v3.MoW) --<----<<<
,MM# = DATEPART(mm,v3.MoW) --<----<<<
FROM (VALUES(DATEDIFF(dd,'0001-01-01',@SomeDate)%7)) v1(Offset)
--Remember, '0001-01-01' is a MONDAY! --<----<<<
CROSS APPLY (VALUES(DATEADD(dd,-Offset,@SomeDate))) v2(SoW)
CROSS APPLY (VALUES(DATEADD(dd,3,v2.SoW))) v3(MoW)
;
Here are the results:Here are the results:
Code for SUNDAY Week Start
To do the same thing but with Sunday Week starts, all we have to do is change the “Reference Date” from a Monday date to a Sunday date. The earliest Sunday available in SQL Server occurs on '0001-01-07' and so we make that small change in our code and we’re done! Nothing else needs to change. Here’s the code:
--===== Find the Year, Quarter, and Month for the week
-- with a SUNDAY start date.
-- "The MoW Knows!"
-- All we did was change the "Reference Date"!
-- Remember, '0001-01-07' is a SUNDAY!
DECLARE @SomeDate DATE = '02 Jan 2015'; --The "Given Date"
SELECT Offset = v1.Offset
,DW# = v1.Offset+1
,SoW = v2.SoW
,EoW = DATEADD(dd,6,v2.SoW)
,SnW = DATEADD(dd,7,v2.SoW)
,MoW = v3.MoW
,YY# = DATEPART(yy,v3.MoW)
,QQ# = DATEPART(qq,v3.MoW)
,MM# = DATEPART(mm,v3.MoW)
FROM (VALUES(DATEDIFF(dd,'0001-01-07',@SomeDate)%7)) v1(Offset)
--Remember, '0001-01-07' is a SUNDAY! --<----<<<
CROSS APPLY (VALUES(DATEADD(dd,-Offset,@SomeDate))) v2(SoW)
CROSS APPLY (VALUES(DATEADD(dd,3,v2.SoW))) v3(MoW)
;Here are the results. Note that while I only have the newer columns highlighted in Blue, ALL of the columns changed because the “Reference Date” changed from a Monday to a Sunday, which is just what we wanted to happen!
Identifying the Week Number of the Year (WK#)
Calculating the “Week Number” of the year is critical to many other calculations that are beyond the scope of this article but… we’re going to build WK# here. And, yup… it’ll also produce the same values as ISOWK if the week start day is set to Monday.
We’ve already discussed the problem with the WEEK (wk) date-part. It’s always “stuck” on Sunday. Worse yet, it resets to “1” on the 1st of January of any year, which also means that it’s NOT identifying a 7 day week, like we have been and need to do.
As a result, most people don’t even try to calculate the week number of the year and those that do frequently end up using end some form of RBAR1 or they resort to using LEAD or LAG. The latter isn’t so bad but, is there a simpler way to calculate the week number of a year that ALSO automatically changes depending on the starting day of the weeks, especially for those nasty edge-case weeks when the year changes?
The answer is, ooooooooooooohhhhhhh yyyyyyyeeeeaaaaahhhh… and it also uses some really simple Integer Math, as well.
Stepping back to the previous section, remember that we discovered that the MoW knows a whole lot about any given week including which Year a week belongs to.
The DY (Day of Year) Date-Part
There’s a handy little date-part that most people have no use for and that’s the DY (Day of Year) date. All it does is it starts counting at “1” on the 1st of January of any year and increments for every day that passes until the next year starts, and then it resets to 1 on the first day of that next year.
So, let’s say we have a year full of days numbered from 1 to 365. How can we break that up into numbered groups of 7 days each to create week numbers??
The first thing is that we need to subtract “1” from the numbers to give us the numbers of 0 through 364, which is necessary to support the Integer Math. That means that week group 1 will contain the days 0 thru 6 and week group 2 will contains days 7 thru 13, etc, etc. What formula can we use to do that mathematically?
Looking at days 0 through 6, which are integers, what values do we get if we divide by 7 (also an integer)? The answer is that 7 “goes into” any of those numbers exactly 0 times with some remainder. Integer Math will automatically discard the remainder and so our answer is zero. We don’t want a “Week 0” so we just have to add “1” to that and we end up with “Week 1”.
The same formula works if we use days 7 thru 13. If we divide any of those days by 7 using Integer Math, we end up with a “1”. But this is really week 2 and so, just like the 0 thru 6 discussion in the previous paragraph, we simply need to add “1” to the answer and we correctly get “Week 2”.
So the formula looks like this.
WK# = (SomeDayNumber-1)/7+1
That’s 3 mathematical operations. Is there a simplification to be had?
The answer is yes. Instead of subtracting 1 to support the Integer Math, lets add 6 to the day number. Adding 6 is like putting the day into the next week. For example, if the day number is 1 and we add 6 to it, we end up with the number 7. Instead of it being the first day of the current week (0), it looks like it’s the first day of the next week, which is week 1.
What that does for us is we no longer need the “+1” in the formula, which adds a week like we previously needed to do.
So, the optimization for the formula looks like this…
WK# = (SomeDayNumber+6)/7
The next question is, where do we get the “DayNumber” from? Again, the MoW knows everything about the week when it comes to date-parts. And, again, the date-part that we need to work with is DY (Day of Year) and so our formula is going to look like the following…
WK# = (DATEPART(dy,v3.MoW)+6)/7
Code for Monday Week Start
Here’s the calendar for that for when the weeks start on Mondays. Again, the Red box is the week we’re working with and the Yellow box identifies the MoW for each week.
In the code that follows, note that we’re using the previous code that uses a “given date” of Friday, 02 January 15 and the “Reference Date” is, once again, set to '0001-01-01', which is a Monday.
--===== Find the WK# with a MONDAY start date.
-- "The MoW Knows!"
-- Remember, '0001-01-01' is a MONDAY!
DECLARE @SomeDate DATE = '02 Jan 2015'; --The "Given Date"
SELECT Offset = v1.Offset
,DW# = v1.Offset+1
,SoW = v2.SoW
,EoW = DATEADD(dd,6,v2.SoW)
,SnW = DATEADD(dd,7,v2.SoW)
,Mow = v3.MoW
,YY# = DATEPART(yy,v3.MoW)
,QQ# = DATEPART(qq,v3.MoW)
,MM# = DATEPART(mm,v3.MoW)
,WK# = (DATEPART(dy,v3.MoW)+6)/7 --<----<<<
FROM (VALUES(DATEDIFF(dd,'0001-01-01',@SomeDate)%7)) v1(Offset)
--Remember, '0001-01-01' is a MONDAY! --<----<<<
CROSS APPLY (VALUES(DATEADD(dd,-Offset,@SomeDate))) v2(SoW)
CROSS APPLY (VALUES(DATEADD(dd,3,v2.SoW))) v3(MoW)
;Here are the results. You can see that even though it’s a partial week for 2015, the MoW knows which year it belongs to.
Code for Sunday Week Start
Here’s the calendar for that for when the weeks start on Sundays. Again, the Red box is the week we’re working with and the Yellow box identifies the MoW for each week.
In the code that follows, note that we’re using the previous code that uses a “given date” of Friday, 02 January 15 and the “Reference Date” is, once again, set to '0001-01-07', which is a Sunday.
--===== Find the WK# with a SUNDAY start date.
-- "The MoW Knows!"
-- We only changed the "Reference Date.
-- No other changes were made!
-- Remember, '0001-01-07' is a SUNDAY!
DECLARE @SomeDate DATE = '02 Jan 2015'; --The "Given Date"
SELECT Offset = v1.Offset
,DW# = v1.Offset+1
,SoW = v2.SoW
,EoW = DATEADD(dd,6,v2.SoW)
,SnW = DATEADD(dd,7,v2.SoW)
,Mow = v3.MoW
,YY# = DATEPART(yy,v3.MoW)
,QQ# = DATEPART(qq,v3.MoW)
,MM# = DATEPART(mm,v3.MoW)
,WK# = (DATEPART(dy,v3.MoW)+6)/7
FROM (VALUES(DATEDIFF(dd,'0001-01-07',@SomeDate)%7)) v1(Offset)
--Remember, '0001-01-07' is a SUNDAY! --<----<<<
CROSS APPLY (VALUES(DATEADD(dd,-Offset,@SomeDate))) v2(SoW)
CROSS APPLY (VALUES(DATEADD(dd,3,v2.SoW))) v3(MoW)
;Here are the results. You can see that even though it’s a partial week for 2014 (not to mention the last week of the year), the MoW knows which year it belongs to and correctly calculated the week number as “53”.
Heh… repeat after me… “The MoW KNOWS!” 😊
Parameterizing the “Reference Date” (@DateFirst)
Ok… we’ve made the columns so that they’ll work as expected no matter which day of the week that we identify as the starting day of the week. We change the starting day of the week by changing the “Reference Date” to a date known to have the starting day of the week we want.
Let’s fix that bit of pain
Changing the string literal for the “Reference Date" is a pain and we certainly don’t need 7 different copies of the code, 1 for each day of the week. We also want to keep the code as simple as it is and, since converting strings to dates has a bit of a poor effect on performance, we also need a fast method without look-ups, string conversions, etc.
Since we’re using dates outside the range of the DATETIME datatype, we can’t use a “Date Serial #” like a lot of the old code does. (A “Date Serial #” is the integer representing the number of days away from the 01 January 1900 Epoch for SQL Server).
It would also be nice if we followed the same standard as DATEFIRST where Monday = 1 and Sunday = 7 because we already know that and it's simple and doesn't require any real fancy formulas.
Exploring the Code
Remember the dates we used for “Reference Dates”? Do you see a pattern there?
The numeric “day”part of those dates very conveniently follows the same pattern as the Monday = 1 and Sunday = 7 pattern. If we created an tiny integer variable called @DateFirst, we could use it in a simple, fast formula to calculate what the “Reference Date” should be.
What’s one of the simplest, fastestest ways to do that? Like this…
DATEFROMPARTS(1,1,@DateFirst)
Here’s the code where we replace the hardcoded "Reference Date" with that bit of computational heaven in the formula that calculates OffSet…
Code for Monday Week Start
--===== Parameterizing the "Reference Date" to work like -- the DATEFIRST setting. (MONDAY TEST) -- We replace the literal date with DATEFROMPARTS(1,1,@DateFirst) -- in the OFFSET calculation and we're DONE! -- Remember, Monday = 1, Sunday = 7 DECLARE @SomeDate DATE = '02 Jan 2015' --The "Given Date" ,@DateFirst TINYINT = 1 ; --<----<<< Test for MONDAY as the starting day of the week SELECT Offset = v1.Offset ,DW# = v1.Offset+1 ,SoW = v2.SoW ,EoW = DATEADD(dd,6,v2.SoW) ,SnW = DATEADD(dd,7,v2.SoW) ,Mow = v3.MoW ,YY# = DATEPART(yy,v3.MoW) ,QQ# = DATEPART(qq,v3.MoW) ,MM# = DATEPART(mm,v3.MoW) ,WK# = (DATEPART(dy,v3.MoW)+6)/7 FROM (VALUES(DATEDIFF(dd,DATEFROMPARTS(1,1,@DateFirst),@SomeDate)%7)) v1(Offset) -- RefDate No Longer HardCoded! --<----<<< CROSS APPLY (VALUES(DATEADD(dd,-Offset,@SomeDate))) v2(SoW) CROSS APPLY (VALUES(DATEADD(dd,3,v2.SoW))) v3(MoW) ;
And here are the results for that run… same as if we used a hard-coded date of '0001-01-01', which is a MONDAY.
Code for Sunday Week Start
In the following code, all we did this time was change the value of @DateFirst from 1 (Monday) to 7 (Sunday).
--===== Parameterizing the "Reference Date" to work like -- the DATEFIRST setting. (SUNDAY TEST) -- All we did was change the value of @DateFirst -- Remember, Monday = 1, Sunday = 7 DECLARE @SomeDate DATE = '02 Jan 2015' --The "Given Date" ,@DateFirst TINYINT = 7 ; --<----<<< Test for SUNDAY as the starting day of the week SELECT Offset = v1.Offset ,DW# = v1.Offset+1 ,SoW = v2.SoW ,EoW = DATEADD(dd,6,v2.SoW) ,SnW = DATEADD(dd,7,v2.SoW) ,Mow = v3.MoW ,YY# = DATEPART(yy,v3.MoW) ,QQ# = DATEPART(qq,v3.MoW) ,MM# = DATEPART(mm,v3.MoW) ,WK# = (DATEPART(dy,v3.MoW)+6)/7 FROM (VALUES(DATEDIFF(dd,DATEFROMPARTS(1,1,@DateFirst),@SomeDate)%7)) v1(Offset) -- RefDate No Longer HardCoded! --<----<<< CROSS APPLY (VALUES(DATEADD(dd,-Offset,@SomeDate))) v2(SoW) CROSS APPLY (VALUES(DATEADD(dd,3,v2.SoW))) v3(MoW) ;
And here are the results for that run… same as if we used a hard-coded date of '0001-01-07', which is a SUNDAY.
The Bonus
Here’s a serious bonus…
In the final code above, we set the @DateFirst parameter to some number that represents the day of the week that we want to use as the first day of every week. I named it @DateFirst for a couple of reasons.
- It’s easy to remember because we already know what the DATEFIRST setting does.
- It uses exactly the same values that we already know the DATEFIRST setting uses.
- You can set it to @@DATEFIRST if you want it to automatically follow DATEFIRST.
That’s the bonus. You can set @DateFirst to a numeric value for when you don’t want it to change if someone changes DATEFIRST or you can pass it the @@DATEFIRST function using @DateFirst = @@DATEFIRST and it’ll automatically change according to whatever the current value of DATEFIRST is.
The dbo.WeekDates Function
Rather than post the function, which has two pages of documentation in the flower-box header, I’ve just attached the function. If you don’t like the column names that I used for the sake of brevity, change them. 😊
The flower-box header contains some usage examples, what the inputs are and what they expect, what the various columns are in the output, some programmer notes, a fair bit of performance info, and more. The actual code is pretty short and quite “DRY”.
Some Changes Were Made
I’ll also tell you that I’ve removed some of the columns that really only serve to explain. For example, neither the Offset nor our friend, the MoW column, are included in the output and I put the columns in a different order than our test code just to group some things together.
Last but not least, I included some “safety” criteria in the WHERE clause. For example, I enforce using only the values of 1 thru 7 for @DateFirst and the function will simply return nothing if that’s violated. I’ve also excluded the very first and very last weeks in the entire range of dates possible in SQL Server since 2008. That shouldn’t be an issue for anyone except the possible historian. If the range is violated, it’ll also return nothing in the output rather than blowing up in your face.
You Can Create Columns for A Calendar Table
A lot of people would rather use a Calendar Table. If that’s your druther, then you can use this function along with the fnTally() function to create the columns either to be added to an existing calendar table or to create a stand-alone table.
Here’s the link where you can get the fnTally() function that I use to easily pull this off: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
You'll find an example of the code (below) you can use to generate all the week information columns for a given range of dates. You don’t have to use all of the columns. I just used “wd.*” to keep this example short. Hopefully it’s obvious that both the dbo.fnTally function and the dbo.WeekDates must already exist in whatever database you’re operating in.
Feel free to substitute your own “series” generator, if you have one, instead of using the fnTally function. If yours contains a recursive CTE, a WHILE loop, or other form of RBAR1, consider switching to something a whole lot faster and less resource intensive, like the dbo.fnTally function. 😉
--===== Drop the temporary table that will contain the information that you can update -- your existing Calendar table from or change the name if this is the only thing -- you want in a new Calendar Table. -- Be advised that I've not provided any indexing. DROP TABLE IF EXISTS #WeekDates; GO --===== Change the values for the obviously named variables below to suit yourself. -- I've just provided an example for the entire 21st century + 1 day. -- This example creates 36,526 in less than a second and occupies only 3.0 MB. DECLARE @StartDT DATE = '01 Jan 2000' ,@EndDT DATE = '01 Jan 2100' -- The extra day ,@DateFirst TINYINT = 1 --(Monday/ISO Compliant) -- Change this to suit ; --===== Create and populate the Temp Table on-the-fly. -- Change, delete, or add any columns to suit yourself. -- Replace wd.* with only those column names you need -- or keep it like it is if you want everything. -- The "yyyyWww" is ISO 8601 compliant for "ISO WEEK" SELECT Date = dd.Date ,DateFirst = CONVERT(TINYINT,@DateFirst) ,DayName = DATENAME(dw,dd.Date) ,MonthName = DATENAME(mm,dd.Date) ,wd.* ,yyyyWww = CONCAT(wd.YY#,'W',RIGHT(wd.WK#+100,2)) --Bonus!! MoW Knows Everything!! INTO #WeekDates FROM dbo.fnTally(0,DATEDIFF(dd,@StartDT,@EndDT)) t CROSS APPLY (VALUES(CONVERT(DATE,DATEADD(dd,t.N,@StartDT)))) dd(Date) CROSS APPLY dbo.WeekDates(dd.Date,@DateFirst) wd ; --===== Have a look at what we've created SELECT * FROM #WeekDates ORDER BY Date ;
Epilogue
We covered some interesting aspects of some of the temporal and DATEDIFF() function in SQL Server and how those aspects make it difficult to bend weeks to our needs.
The reason why we need to consider weeks is because of this simple and frequently asked question….
How do I get the first day of the week?
Then we identified many of the other “week parts” that we might want to determine.
Then we calculated some of the key parts that provide the basis of all those other parts as well as answering the question above. In the process, we learned that the MoW (Middle of Week) date knows just about everything we need.
From there, we went nuts and quickly solved for a myriad of other “week parts” including the elusive correct WK# (Week Number of Year) using some really simple math. We followed that up with parameterizing the “Reference Date” and the first day of the week we want to use and showed how to using for the typical 1 to 7 days of the week to start weeks on so that DATEFIRST can be ignored and then demonstrated that setting that parameter to @@DATEFIRST can make the code automatically change its calculations based on the value of the DATEFIRST setting.
In the end, a well-documented function that’s ready for production use was provided and code was provided to demonstrate how to use it to augment an existing Calendar table or create a new stand-alone table with just the week information in it with 1 row for each day for a range of dates.
Heh… then our heads exploded. 😊
Thanks for listening, folks.
-- Jeff Moden
1 "RBAR” is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"
© Copyright - Jeff Moden, 25 Jun 2022, All Rights Reserved
