<

A SQL Tidbit: What week is it?

Published on
9,626 Points
3,226 Views
4 Endorsements
Last Modified:
Approved
Kevin Cross
Father, husband and general problem solver who loves coding SQL, C#, Salesforce Apex or whatever.
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
;

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
;

Open in new window

Listing 2 Results A
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
;

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:
What are the application needs (i.e., is having January first represented as 0, 52, or 53 acceptable)?
Is the solution efficient?

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!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
4
Comment
Author:Kevin Cross
  • 3
  • 2
5 Comments
 
LVL 47

Expert Comment

by:aikimark
@Kevin

I thought you were going to use a Numbers/Tally table for this. :-)
0
 
LVL 61

Author Comment

by:Kevin Cross
I did. ;)
I just used a built-in one with master..spt_values given I only needed a small amout of dates to prove my point.

Thanks for reading, Mark. It is much appreciated.

Regards,
Kevin
0
 
LVL 47

Expert Comment

by:aikimark
@Kevin

Suggestion:
I suggest you show the week values from the same date ranges in your pictures.  As it is, the reader sees apples and oranges.

>>I thought you were going to
I guess that should have been I thought you might
0
 
LVL 61

Author Comment

by:Kevin Cross
They were meant to.  The first picture is to show that the week in question is not week 24 accoding to standard DATEPART(WEEK, ...) function and the second is to illustrate what ISO_WEEK does to January.  However, I do see your point. *smile*
0
 
LVL 61

Author Comment

by:Kevin Cross
Thanks again for the suggestion, Mark! Please find a new pretty picture attached. *smile*
0

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month