A SQL Tidbit: What week is it?

In this article—a derivative of my DaytaBase.org blog post—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server.

First, to frame this study, I will offer a little background.  I was participating in the question "sql weeknumber issue!".  The gist of the jamppi's challenge was to find why—and ultimately fix the issue of—when selecting data for this week, data for last week was showing up.  The asker's perspective is that the current week is the twenty-fourth week of the year; last week, the twenty-third.  Further, the beginning of the week is Monday, so note from this point forward that DATEFIRST has been set to '1'.

Additionally, for demonstration purposes, we will take advantage of the master..spt_values table to quickly generate some "random" dates to learn with.

Listing 1:

;WITH randomDates(aDate) AS (
                         SELECT DATEADD(DAY, -[number], '2011-06-18')
                         FROM master..spt_values
                         WHERE [type] = 'p'
                      )
                      SELECT aDate
                           , DATEPART(WEEK, aDate) AS WK
                      FROM randomDates
                      ;

Select all Open in new window

Listing 1 produces 2048 dates ranging from '2005-11-09' to '2011-06-18' along with their respective week of year values using the DATEPART(…) function.  For more information on spt_values, please read "Fun with MS SQL spt_values for delimited strings and virtual calendars" by Microsoft SQL Server MVP Mark Wills.

Again, this is for illustration of my point only, so do not get too lost in the "wow, how did I not know that was there" feeling for the moment—but do research it later as it is very useful.

The Problem

SynSat-20110618-Listing1Results.png

Getting back to the output of Listing 1 above, take note of the week number for '2011-06-16' or Thursday.  The reason is that the "WEEK" argument passed to DATEPART(…) always calculates January first as week one.  Therefore, in a year such as this, the first Monday in the year is actually in week two.

A Solution

Beginning with Microsoft SQL Server 2008, DATEPART(…) added a new possible value for its datepart argument called ISO_WEEK.  Consequently, ISO_WEEK came to mind as a solution to the user's problem.

Listing 2:

;WITH randomDates(aDate) AS (
                         SELECT DATEADD(DAY, -[number], '2011-06-18')
                         FROM master..spt_values
                         WHERE [type] = 'p'
                      )
                      SELECT aDate
                           , DATEPART(ISO_WEEK, aDate) AS WK
                      FROM randomDates
                      ;

Select all Open in new window

As shown by the updated results, this solution will work—pending SQL 2008 or higher is available—but is based on a very specific set of rules outlined in ISO 8601 standard.  Caveats from the standard include the possibility of the first three days of January calculating out to the last week of the previous year; or the last three days of December, to week one of the following year.  The below illustrates the former as well as confirming Monday, January third as the start of week one.
  SynSat-20110618-Listing2Results.png

Very good!  At this point, I will consider this acceptable.  However, users on SQL Server 2005 or lower cannot carry out this technique.  Hmmm, time to go back to the drawing board.

Not diving too deep into a boring explanation of the ISO 8601 standard, in short it turns out that it has a fairly straight-forward formula to calculate the ISO week number from a Gregorian date.

Note: There are years where the formula results in a zero for one to three days at the beginning of January and 53 for one to three days at the end of December, incorrectly.

The former is always set to the last week of the previous year, where the latter takes a little more work.  The simple decision  for dates after December 28, if the day of week is Monday to Wednesday (Nota Bene: The week with first Thursday of a year is usually week one), then that day is set to the same week number as the first of January.  A complete code listing is below.

Listing 3:

;WITH randomDates(aDate) AS (
                         SELECT DATEADD(DAY, -[number], '2011-06-18')
                         FROM master..spt_values
                         WHERE [type] = 'p'
                      ), baseISOWeekFormula(aDate, WK_ISO, WK_PRV, WK_NXT) AS (
                         SELECT aDate
                              -- http://en.wikipedia.org/wiki/ISO_8601
                              -- {day of the year} - {day of the week, 1=Monday...7=Sunday} + 10 / 7
                              , (DATEPART(DAYOFYEAR, aDate)-DATEPART(WEEKDAY, aDate) + 10) / 7
                              -- if above is 0, then use previous week (last of previous year)
                              , (DATEPART(DAYOFYEAR, DATEADD(DAY, -3, aDate))
                                -DATEPART(WEEKDAY, DATEADD(DAY, -3, aDate)) + 10) / 7
                              -- if above is 53, it may be week 1 of following year
                              , (DATEPART(DAYOFYEAR, DATEADD(DAY, +3, aDate))
                                -DATEPART(WEEKDAY, DATEADD(DAY, +3, aDate)) + 10) / 7
                         FROM randomDates
                      )
                      SELECT aDate
                           -- user-defined scalar function likely best implementation
                           -- showing as inline SQL just as an example
                           ,  CASE
                                WHEN aDate > CONVERT(DATE, DATENAME(YEAR, aDate)+'1228', 112)
                                      AND DATEPART(WEEKDAY, aDate) < 4 -- 4=Thursday
                                      AND WK_ISO = 53
                                   THEN COALESCE(NULLIF(WK_NXT, 0), 53)
                                ELSE COALESCE(NULLIF(WK_ISO, 0), WK_PRV)
                             END AS WK
                      FROM baseISOWeekFormula
                      ;

Select all Open in new window

Voilà!

The technique shown above can be modified to work with any version of SQL Server, but note as written with common table expressions will only run "as-is" on SQL 2005 or higher.  

Conclusion

Using ISO week is not the only solution, but is a very nice attempt and, with SQL 2008, simple.  It intrigued me.  Since the functionality did not exist in SQL 2005, it was a challenge that I could try to solve.  I would urge readers that are curious to experiment with other solutions themselves.  For example, what if you found the first occurrence of a given day of the week in a year and then used that date as the reference point for calculating weeks since that point.  Or consider how DATEFIRST could be used.

Things to consider on your journey should include:

For the latter, both the accepted solution and mine above fail.  The function around the DATETIME column makes it not SARGable.  In hindsight, a recommendation to calculate the start and end dates would be better; however, this again was purely for research and so is not intended to be the best or only solution.

Challenge:
If anyone can think of a solution that is elegant or otherwise interesting, please post in comments below.

Thanks for reading and enjoy!

Best regards and happy coding,

Kevin C. Cross, Sr. (mwvisa1)


Related references:
Microsoft SQL Server Date and Time Functions BOL | http://msdn.microsoft.com/en-us/library/ms186724.aspx#DateandTimeFunctions

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you found this article helpful, please click the Yes button after the question just below. This will give me (the author) a few points and might encourage me to write more articles.

If you didn't or otherwise feel the need to vote No, please first leave a comment to give me a chance to answer and perhaps to improve this article.

Thank you!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=