Correction - 23 Nov 2013
I've made no changes in the article starting with the "Introduction" because it still correctly describes the original formula written by "t-clausen.dk".
As is the nature of this fine community, if there's a problem with something in an article, it is quickly ferreted out by those who participate in the discussion. This article is no exception and I thank Peter Larsson who discovered that there was a problem.
The original formula has a problem that I didn't originally test for and, that is, if you use the formula for dates earlier than 1900-01-01, the calculation of the correct ISOWeek is by chance rather than by plan. The problem is with "Step 3 – Convert the Date Serial to the "Day of the Year" where a difference in days is used in the calculation instead of the actual date. This works just fine for dates greater than or equal to 1900-01-01 because that difference in days matches SQL Server's underlying date serial numbers but it doesn't work correctly for dates that have a date serial number less than 0 (1900-01-01).
I had the article taken "offline" until I could fix it and test it.
I found the fix and that was to add the difference in dates to the "base date" instead of using the difference in dates as a date serial number. Here's that code.
SELECT (DATEPART(dy,DATEADD(dd,DATEDIFF(dd,'1753',@SomeDate)/7*7+3,'1753'))+6)/7 ;
Peter verified the modified formula and made an additional optimization that removed one of the additions which saves a bit on CPU time. Here's that code and that's the one I recommend that you use if you have the need (< SQL Server 2008). The modifications only cause a scant increase of a dozen or so milliseconds over a million row test so it's still a competitor with the ISOWEEK date-part of SQL Server 2008 and up and still works in earlier versions.
SELECT (DATEPART(dy,DATEADD(dd,DATEDIFF(dd,'17530101',@SomeDate)/7*7,'17530104'))+6)/7 ;
Introduction
I was working on a thread that asked how to create a "Broadcast Calendar" ( http://en.wikipedia.org/wiki/Broadcast_calendar ) and, to make a much longer story shorter, it dawned on me that I might be able to solve the problem using a calculation similar to an "ISO Week Number" calculation. How to build a “Broadcast Calendar” will have to wait until another article, though, because I found something else that’s very cool.
Very fortunately for me, I had misplaced my previously written T-SQL solution to solve for ISO Week Numbers and decided to do a quick web search to find one instead of trying to find my own. I wanted one that I could use as a high performance "iSF" (Inline Scalar Function) like the one I had built.
I say "very fortunately" because I couldn't believe my good luck with what I found. Yes, I know that SQL Server 2008 and up has a wonderful ISO_Week DATEPART that can be used to return the ISO Week Number as an integer, but what about folks (like me) still "stuck" with SQL Server 2005 or less?
I had looked at several of the "iSF capable" functions and was just about to settle on using one of them but decided to look at just one more post. I have to tell you, it was it a bit like hitting a lottery. What I found was an absolutely brilliant and simple formula that had me banging my head on my desk and yelling "It's SO simple! Why didn't I think of that!?"
Here's the link to the thread that had me trying to flatten out the high spots on my head: http://stackoverflow.com/questions/7330711/isoweek-in-sql-server-2005 (look for the entry by "t-clausen.dk")
The purpose of this article is to not only share this wonderful find but to also explain how it works and to express my appreciation to the author of a really fine bit of SQL prestidigitation. Well done, Mr. "t-clausen.dk"!
What is an "ISO Week Number"?
Calendars are a real pain in the patooti, especially for businesses, mostly because the calendar year can start on any day of the week. That usually leaves a partial week at both the beginning an end of the year but not always. The ISO (International Standards Organization or International Organization for Standards, depending on where you hail from) came up with a remedy for that problem in the form of an "ISO Week Number".
If you're reading this article, then you probably already know what an "ISO Week Number" is and what some of the advantages of using it are. If you don't, please see the following WikiPedia article for more information: http://en.wikipedia.org/wiki/ISO_8601
A Missed "KISS".
The WikiPedia article is a beautiful article with lots and lots of information on the subject. In fact, and like many other articles on the subject, there's almost too much information on ISO Week Numbers especially when trying to figure out how to calculate the value for any given date.
As a result of the "apparent" complexity of the problem, many people have come up with solutions ranging from 100 line monstrosities to smaller code that looks like it couldn't get any simpler. If you do a web search for "SQL Server ISO Week", you'll find hundreds of examples of what I'm talking about.
Like so many others, I forgot to "KISS" (a well known acronym for "Keep It Simple, Stupid") the problem. Despite all the information about how to determine what the first ISO Week Number for any given year is, the key to it all is incredibly simple: just count the number of Thursdays in the year and you have the solution.
Of course, some of the solutions you'll find in your search do just that, but they really jump through some hoops getting there. There's actually a very simple solution to counting the Thursdays in a year. SQL Server has a DATEPART function that will tell you what day of the year a given date is. "All" we have to do is find the day of the year for the Thursdays for each week and divide by 7, the number of days in a week. There's a little trick we have to do to offset that for rounding and to start the count at "1" instead of "0", but that's the basic "KISS" method for solving the problem in a super simple, high performance, and extremely elegant manner.
Solving the "ISO Week Number" Problem
Let's cut to the chase. Here's the absolutely brilliant formula that t-clausen.dk came up with that might get you banging your head like I did.
SELECT ISOWeek = (DATEPART(dy,DATEDIFF(dd,0,SomeDateColumn)/7*7+3)+6)/7 FROM dbo.TestTable ;
Like I said, it's absolutely brilliant and my mathematical hat is off to by t-clausen.dk for its elegance. Once we go through how it works, you'll appreciate its incredible simplicity. Considering that there are no character-based conversions of the data, you'll also appreciate its speed.
Generating Test Dates in a Table
Before we can tear the formula apart and see how it works, we need to make some dates to test with. Well do this with help of a "Tally Table". I'm using a physical "Tally Table" instead of a function based on Itzik Ben-Gan's method for creating sequential numbers because I wanted folks using SQL Server 2000 or less to be able to play along. I also don't use any CTEs for the same reason.
If you don't know what a "Tally Table" (or equivalent function) is or how it can be used to replace certain types of loops, please see the following article: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.
My "standard" Tally Table starts at "1" and contains sequential numbers up to 11,000, which can be used to calculate more than 30 years worth of days. By adding those sequential numbers to a date, we can easily generate up to 11,000 sequential dates. I also wanted to make it safe so we'll do this in a nice, safe place that everyone has, TempDB.
Here's the code. It will build a test table of 3002 dates from 24 Dec 2009 through 07 Jan 2019. I’ll stress it one more time; this code will work on all versions with just a minor tweak.
--===== Do this in a nice, safe place that everyone has.
USE tempdb
;
--===== Define the start and end dates for the testing.
DECLARE @StartDate DATETIME
, @EndDate DATETIME
;
SELECT @StartDate = '2009-12-24'
, @EndDate = '2019-01-07'
;
--===== Conditionally drop the tables to make reruns in
-- SSMS easier. The 3 part naming is overkill but
-- I want to make sure we don't accidently drop a
-- real table.
IF OBJECT_ID('tempdb.dbo.TestTable','U') IS NOT NULL
DROP TABLE tempdb.dbo.TestTable
;
IF OBJECT_ID('tempdb.dbo.Tally','U') IS NOT NULL
DROP TABLE tempdb.dbo.Tally
;
--===================================================================
-- Create a Tally table from 1 to 11000
--===================================================================
--===== Create and populate the Tally table on the fly.
-- Obviously, if you already have one, you don't need to do this
-- but I wanted those that didn't already have on to be able to
-- participate. Change the tables in the FROM clause to
-- master.dbo.syscolumns if using SQL Server 2000 or earlier.
SELECT TOP 11000
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
--===== Add a CLUSTERED Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
;
--===================================================================
-- Create and populate the test table.
--===================================================================
--===== Create and populate the test table on-the-fly.
-- Obviously, this depends on a Tally Table being
-- present in TempDB. Change that to wherever
-- your Tally Table is or what it's named.
SELECT Date = DATEADD(dd,t.N-1,@StartDate)
INTO dbo.TestTable
FROM dbo.Tally t
WHERE t.N < DATEDIFF(dd,@StartDate,@EndDate)+2
ORDER BY t.N
;
GONow, let's run the new found ISO Week Number formula using the dates in the test table and see that it works.
SELECT Date = CONVERT(CHAR(10),Date,120)--Display purposes only , DOW = LEFT(DATENAME(dw,Date),9) --Here just for reference , ISOWeek = (DATEPART(dy,DATEDIFF(dd,0,Date)/7*7+3)+6)/7 FROM dbo.TestTable ;
If we look at the first 3 weeks of that output, we can see that 2009 is one of those odd years that have 53 ISO Weeks in them. We can also see that the first 3 days of 2010 fall into the 2009 ISO Year. If you check all of the other dates, especially those during the transition weeks where the year changes, you'll find that the formula works correctly in that the first week of every ISO Year contains the first Thursday of the year. You'll find out that leap years are also handled correctly.
Date DOW ISOWeek
---------- --------- -----------
2009-12-24 Thursday 52
2009-12-25 Friday 52
2009-12-26 Saturday 52
2009-12-27 Sunday 52
2009-12-28 Monday 53
2009-12-29 Tuesday 53
2009-12-30 Wednesday 53
2009-12-31 Thursday 53
2010-01-01 Friday 53
2010-01-02 Saturday 53
2010-01-03 Sunday 53
2010-01-04 Monday 1
2010-01-05 Tuesday 1
2010-01-06 Wednesday 1
2010-01-07 Thursday 1
2010-01-08 Friday 1
2010-01-09 Saturday 1
2010-01-10 Sunday 1
2010-01-11 Monday 2
2010-01-12 Tuesday 2
2010-01-13 Wednesday 2
2010-01-14 Thursday 2
Step 1 – Calculate what the Monday of the Week for the Current Date is
Behind each date in the DATETIME datatype, there are two integers. One contains the number of days that have occurred since the first day of January, 1900 and the other represents the time of day. That means that the first day of January, 1900 can be represented by a "0". This underlying number is referred to as a "Date Serial Number". In the code that follows, I refer to Date Serial Numbers simply as "DS".
It also turns out that the first of January, 1900 was a Monday (as is 01 January 1753 if you need to change the calculation to handle dates prior to 1900). The new ISO Week Number formula exploits that fact.
Let's look at the part of the formula I'm talking about. I also list the Date, Day of Week, and ISO Week formula for easy reference and comparison. Note that the Date and DOW columns are formatted just to make them prettier in this article.
SELECT Date = CONVERT(CHAR(10),Date,120)--Display purposes only , DOW = LEFT(DATENAME(dw,Date),9) --Here just for reference , ISOWeek = (DATEPART(dy,DATEDIFF(dd,0,Date)/7*7+3)+6)/7 , DSMonday = DATEDIFF(dd,0,Date)/7*7 FROM dbo.TestTable ;
Referring to the DSMonday calculation (which is a part of the ISOWeek formula), the DATEDIFF part of the code calculates the whole number of days since the first day of January, 1900, which is abbreviated simply as day "0". This also has the pleasant side effect of stripping off any time that may be part of the Date column because of the conversion to an Integer (actually, it sets the time to "midnight" making a "whole day").
Since we're working with ISO Weeks, we want to find the first day of the week for the given Date. Fortunately, ISO Weeks start on Monday so this stays easy. The DATEDIFF produces an INTEGER. If we divide by 7 (the number of days in a week), which is also an INTEGER, SQL Server will do what is known as "Integer Math" which drops any remainder, decimal or otherwise. What we end up with is the number of whole weeks since 01 Jan 1900.
We multiply that result by 7 and what we end up with is the Date Serial Number for the Monday of the week that the Date column is in.
But we need to know the Date Serial Number for the Thursday of the week the Date column is in. (Note that the optimization of using “3” or “-4” instead of “0” here will mess up other calculations and produce an incorrect answer, in this case).
Step 2 – Calculate what the Thursday of the Week for the Current Date is
This is a no-brainer. No matter how you swing it, Thursday always come 3 days after Monday in the Gregorian Calendar. All we have to do is add 3 days to the Monday Date Serial Number and we have the Date Serial Number for Thursday.
Here's the code. I left the DSMonday calculation in the SELECT for easy comparison.
SELECT Date = CONVERT(CHAR(10),Date,120)--Display purposes only , DOW = LEFT(DATENAME(dw,Date),9) --Here just for reference , ISOWeek = (DATEPART(dy,DATEDIFF(dd,0,Date)/7*7+3)+6)/7 , DSMonday = DATEDIFF(dd,0,Date)/7*7 , DSThursday = DATEDIFF(dd,0,Date)/7*7+3 FROM dbo.TestTable ;
Step 3 – Convert the Date Serial to the "Day of the Year"
One of the pieces of magic in this code is the realization that these date/time functions will automatically convert Date Serial Numbers to a DATETIME value before acting upon it. DATEPART is no exception. If we use the "dy" (Day of Year) functionality of DATEPART, it will first convert the DSThursday value to a DATETIME and then correctly calculate the "Day of the Year", which will return scalar values from 1 to 365 on normal years and 1 to 366 on Leap Years.
Here's the code that demonstrates that. As we've been doing for the other sections, I've left all of the other interim calculations in place just for comparison purposes and will continue to do so in all other examples that follow.
SELECT Date = CONVERT(CHAR(10),Date,120 , DOW = LEFT(DATENAME(dw,Date),9) , ISOWeek = (DATEPART(dy,DATEDIFF(dd,0,Date)/7*7+3)+6)/7 , DSMonday = DATEDIFF(dd,0,Date)/7*7 , DSThursday = DATEDIFF(dd,0,Date)/7*7+3 , DoYThursday = DATEPART(dy,DATEDIFF(dd,0,Date)/7*7+3) FROM dbo.TestTable ;
Here are the first 3 weeks from that code.
Date DOW ISOWeek DSMonday DSThursday DoYThursday
---------- --------- ----------- ----------- ----------- -----------
2009-12-24 Thursday 52 40166 40169 358
2009-12-25 Friday 52 40166 40169 358
2009-12-26 Saturday 52 40166 40169 358
2009-12-27 Sunday 52 40166 40169 358
2009-12-28 Monday 53 40173 40176 365
2009-12-29 Tuesday 53 40173 40176 365
2009-12-30 Wednesday 53 40173 40176 365
2009-12-31 Thursday 53 40173 40176 365
2010-01-01 Friday 53 40173 40176 365
2010-01-02 Saturday 53 40173 40176 365
2010-01-03 Sunday 53 40173 40176 365
2010-01-04 Monday 1 40180 40183 7
2010-01-05 Tuesday 1 40180 40183 7
2010-01-06 Wednesday 1 40180 40183 7
2010-01-07 Thursday 1 40180 40183 7
2010-01-08 Friday 1 40180 40183 7
2010-01-09 Saturday 1 40180 40183 7
2010-01-10 Sunday 1 40180 40183 7
2010-01-11 Monday 2 40187 40190 14
2010-01-12 Tuesday 2 40187 40190 14
2010-01-13 Wednesday 2 40187 40190 14
2010-01-14 Thursday 2 40187 40190 14
Just a reminder. The DoYThursday column contains the "Day of the Year" for the Thursday of the week that the Date column is in. If you look at the row containing 2009-12-31, which just happens to be a Thursday, we can see that there were 365 days in the year and that whole week, which includes part of 2010, has a value of 365.
Step 4 – Prepare for More Integer Math
You might think that all we have to do now is divide the DoYThursday value by 7 to get the week number. Nothing could be further from the truth, though.
For example, if the first Thursday of the year occurred on the 4th day of the year, we'd end up with the condensed formula of 4/7 and, because that's Integer Math, the result would be "0". ISO Week Numbers start at "1" so it would seem that just adding "1" to the answer would do the trick.
Ah, but wait. If we look at the previous listing of the first 3 weeks, the first Thursday of 2010 occurs on day number "7" and 7/7 = 1. If we add "1" to that, 2010 would start off at a week number of "2" and as a good friend of mine once said, "That ain't right".
You could go through a bunch of gyrations to figure out that if that first week has a value of "7" that you wouldn't add "1". That would make for much longer and slower code especially since you’d have to do that for all the weeks of the given year but maybe not the previous or next years. Instead, let's once again consider the miracle of Integer Math. If we add 6 days to the Date Serial Number before we do the division, it's like adding .9999999 to some number before doing brute force rounding. So, the "7" would become "13". If we divide that by "7", we'd end up with the condensed formula of 13/7. In Integer Math, the remainder is simply dropped so we'd end up with the correct answer of "1".
Let's look at the opposite end of the spectrum. If the first Thursday of the year is also the first day of the year, the condensed formula would be (1+6)/7 or 7/7. As we've already seen, that would also result in the correct answer of "1".
Looking at the end of 2009, we see the Thursday of that week was also the last day of the year or 365. 2009 is also one of those years where there are 53 ISO Weeks. The condensed formula there would be (365+6)/7 or 371/7, which is the correct value of 53. It all works correctly for everything in between, as well.
Here's the code that demonstrates adding 6 to accommodate the upcoming Integer Math.
SELECT Date = CONVERT(CHAR(10),Date,120) , DOW = LEFT(DATENAME(dw,Date),9) , ISOWeek = (DATEPART(dy,DATEDIFF(dd,0,Date)/7*7+3)+6)/7 , DSMonday = DATEDIFF(dd,0,Date)/7*7 , DSThursday = DATEDIFF(dd,0,Date)/7*7+3 , DoYThursday = DATEPART(dy,DATEDIFF(dd,0,Date)/7*7+3) , Added6Days = DATEPART(dy,DATEDIFF(dd,0,Date)/7*7+3)+6 FROM dbo.TestTable ;
Here are the first 3 weeks of results.
Date DOW ISOWeek DSMonday DSThursday DoYThursday Added6Days
---------- --------- ------- -------- ---------- ----------- ----------
2009-12-24 Thursday 52 40166 40169 358 364
2009-12-25 Friday 52 40166 40169 358 364
2009-12-26 Saturday 52 40166 40169 358 364
2009-12-27 Sunday 52 40166 40169 358 364
2009-12-28 Monday 53 40173 40176 365 371
2009-12-29 Tuesday 53 40173 40176 365 371
2009-12-30 Wednesday 53 40173 40176 365 371
2009-12-31 Thursday 53 40173 40176 365 371
2010-01-01 Friday 53 40173 40176 365 371
2010-01-02 Saturday 53 40173 40176 365 371
2010-01-03 Sunday 53 40173 40176 365 371
2010-01-04 Monday 1 40180 40183 7 13
2010-01-05 Tuesday 1 40180 40183 7 13
2010-01-06 Wednesday 1 40180 40183 7 13
2010-01-07 Thursday 1 40180 40183 7 13
2010-01-08 Friday 1 40180 40183 7 13
2010-01-09 Saturday 1 40180 40183 7 13
2010-01-10 Sunday 1 40180 40183 7 13
2010-01-11 Monday 2 40187 40190 14 20
2010-01-12 Tuesday 2 40187 40190 14 20
2010-01-13 Wednesday 2 40187 40190 14 20
2010-01-14 Thursday 2 40187 40190 14 20
Step 5 – Divide the "Up-Rounded" Number of Days by 7 to Get the ISO Week Number
We already have this formula in all of the code that we posted. Simply dividing the "up-rounded" number of days (the Addedd6Days column) by 7 using Integer Math will correctly calculate the ISO Week Number. Again, the final formula is listed in this next snippet of code, which is what we started out to begin with.
SELECT Date = CONVERT(CHAR(10),Date,120) , DOW = LEFT(DATENAME(dw,Date),9) , ISOWeek = (DATEPART(dy,DATEDIFF(dd,0,Date)/7*7+3)+6)/7 FROM dbo.TestTable ;
Before You Say Anything…
Yes, I'm very aware of the fact that you can't do direct addition to the relatively new date/time datatypes such as the DATE datatype. Look carefully at the formula. No where did we violate such a rule. We calculated the number of days using DATEDIFF and that produced an Integer. We divided that number by 7, multiplied it by 7, and added 3 to it. That was all Integer Math. We used that number with DATEPART which implicitly converted the number to a date and then converted that date to the “Day of Year”, which is also an Integer. We then took that Integer, added 6 to it and divided it by 7. It was ALL Integer Math.
How's the Performance?
If you know me, you just had to know a million row test was coming next. 😉 Why would we test such a thing? Because if you don’t yet have SQL Server 2008 or better and you use the new formula there, you might think that you’d have to change the code to use the new ISO_Week DATEPART functionality after you upgrade for performance reasons. You don’t and the following code shows why not.
The code builds a million random dates with times (DATETIME in this case) and compares the new ISO_Week DATEPART against the formula above.
Here's the code for the whole shootin' match. The code calculates the answers and dumps the results to a variable to take the display times out of the picture. Each test is executed 5 times in a row by using “GO 5”
--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate a million row test table with random dates/times
-- where (01 Jan 2000) <= Date < (01 Jan 2020)
SELECT TOP 1000000
Date = CAST(RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'2000','2020')+DATEDIFF(dd,0,'2000') AS DATETIME)
INTO #TestTable
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
GO
--===== Test ISO_Week DATEPART ==========================================================
DECLARE @BitBucket INT;
SELECT @BitBucket = DATEPART(isowk,Date)
FROM #TestTable;
GO 5
--=======================================================================================
GO
--===== Test the new formula ============================================================
DECLARE @BitBucket INT;
SELECT @BitBucket = (DATEPART(dy,DATEDIFF(dd,0,Date)/7*7+3)+6)/7
FROM #TestTable;
GO 5
Here are the results from SQL Profiler. The duration is in milliseconds, just to keep things simple.
Doing the math to take the average duration of each method, we end up with a difference of only 34 milliseconds difference between the two… on a million rows.
Mr. "t-clausen.dk". You did one hell of a nice job on that formula of yours.
Conclusion
I should probably change my name to "Lumpy" because I'm still banging my head about missing such an elegantly simple and very high performance formula myself all these years. It can easily be turned into either a Scalar (ugh!) User Defined Function or into a much higher performance iSF. Mr. "t-clausen.dk" did an awesome job on this formula. If you get the chance, stop by the following link and give his entry a “+1” for his efforts. It’s the least we can do. Here’s that link, again.
http://stackoverflow.com/questions/7330711/isoweek-in-sql-server-2005 (look for the entry by t-clausen.dk)
If you really want to show your appreciation, we could all join together and bang our heads hard enough so he can hear it all the way over in Denmark. 😉
Thanks for listening, folks.
