Best Week Ever


SQL Server provides the ability to find the week of the year in which a given date lies.  For example:



declare @d datetime
set @d = ’13 november 2005′
select datepart(ww, @d)


The result of this is 47.


Now, the problem with this is that the correct result according to ISO 8601 is 45, not 47.  Why?  Because ISO 8601 specifies that:



  • The first day of the week is Monday, not Sunday.
  • The first week of the year is the first week with at least 4 days in it.

Unfortunately, SQL Server defines the first week of the year as the week in which 1 January lies.  (Some of us would argue that this makes more sense, but ISO 8601 says what ISO 8601 says.)


What’s a poor database developer to do?  Wait until the next release of SQL Server?


Well, using VB.NET and CLR integration the solution is very easy.  Our friends over at VB.NET have included exactly the method we need: a very flexible DatePart method that we can wrap and load into SQL Server.


Here’s the source:



Partial Public Class UserDefinedFunctions
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function WeekInYear(ByVal dt As DateTime) As Integer
        Return DatePart(“ww”, dt, FirstDayOfWeek.Monday, _
          FirstWeekOfYear.FirstFourDays)
    End Function
End Class


We can now rewrite our example:



declare @d datetime
set @d = ’13 november 2005′
select dbo.WeekInYear(@d)


And the result is 45.  ISO 8601 would be proud.


Here’s to better living through CLR integration.


Cheers,
-Isaac K. Kunen, Microsoft SQL Server


Comments (11)

  1. PP says:

    Is this function (WeekInYear) available in C# or its just in VB.NET?

  2. sqlclr says:

    The DatePart method is a method defined in the Visual Basic assembly. If you include assembly as a reference in Visual Studio and put a "using Microsoft.VisualBasic" line in your C# class you can then call the method from C# as "DateAndTime.DatePart(…)".

    Cheers,

    -Isaac K. Kunen, Microsoft SQL Server

  3. Robert Varga says:

    Is there any reason why you can’t just use SET DATEFIRST, or am I missing something?

  4. staceyw says:

    And if your in c# and don’t want to load all of VB runtime, then here is a native c# solution:

    using System;

    using System.Data;

    using System.Data.SqlClient;

    using System.Data.SqlTypes;

    using Microsoft.SqlServer.Server;

    public partial class UserDefinedFunctions

    {

    /// <summary>

    /// Gets ISO week number for the date.

    /// </summary>

    /// <param name="date">A DateTime.</param>

    /// <returns>The int week number that date falls within.</returns>

    [Microsoft.SqlServer.Server.SqlFunction]

    public static int WeekInYear(DateTime date)

    {

    return GetISOWeekInYear(date);

    }

    /// <summary>

    /// Returns the ISO week number of the year that date is within.

    /// </summary>

    /// <param name="date">DateTime representing a day in a week.</param>

    /// <returns>Week number.</returns>

    public static int GetISOWeekInYear(DateTime date)

    {

    int weekNum = 1;

    // Get the ISO week containing the 4th day of Jan. This will always be the first ISO week of the year.

    DateTime startOfWeek = GetStartOfWeekISO(new DateTime(date.Year, 1, 4));

    if (date.Date < startOfWeek)

    {

    // Date is before the first Monday of the year. Jan 1, 2005 and Jan 2 2005 are examples as they are in last week of 2004.

    return GetISOWeekInYear(new DateTime(date.Year-1, 12, 31));

    }

    DateTime endOfWeek = GetEndOfWeekISO(startOfWeek);

    while (true)

    {

    if (date <= endOfWeek)

    return weekNum;

    endOfWeek = endOfWeek.AddDays(7);

    weekNum++;

    }

    }

    /// <summary>

    /// Returns the first day of the week which is the Monday in the week represented by date.

    /// </summary>

    /// <param name="date">DateTime representing a day in the week.</param>

    /// <returns>DateTime of the Monday in the week.</returns>

    public static DateTime GetStartOfWeekISO(DateTime date)

    {

    date = date.Date;

    int dow = (int)date.DayOfWeek;

    if (dow == 0)

    dow = 7;

    // Get the Monday of this week.

    DateTime day = date.AddDays(-(dow – 1));

    return day;

    }

    /// <summary>

    /// Returns the last day of the ISO week which is the Sunday in the week represented by date.

    /// </summary>

    /// <param name="date">DateTime representing a day in the week.</param>

    /// <returns>DateTime of the Sunday in the week.</returns>

    public static DateTime GetEndOfWeekISO(DateTime date)

    {

    DateTime dt = GetStartOfWeekISO(date).AddDays(6);

    return GetEndOfDay(dt);

    }

    /// <summary>

    /// Returns the end of the day represented by date.

    /// This maximizes the hours, minutes, seconds, and milliseconds of the date instance.

    /// </summary>

    /// <param name="date">DateTime to operate on.</param>

    /// <returns>DateTime representing the end of the day.</returns>

    public static DateTime GetEndOfDay(DateTime date)

    {

    return new DateTime(date.Year, date.Month, date.Day, 23, 59, 59, 999);

    }

    };

    –William Stacey[MVP]

  5. staceyw says:

    "Is there any reason why you can’t just use SET DATEFIRST, or am I missing something?"

    Yes, it does not work for this. It returns 46, not 45.

  6. The other day, colleague Shawn Steele posted in his blog about the ISO 8601 Week of Year format in Microsoft…

  7. Anderson Menezes says:

    A possible refactoring (extend for year information) of Shawn Steele’s code

       class ISOCalendar

       {

           // Need a calendar.  Culture’s irrelevent since we specify start day of week

           private static Calendar cal = CultureInfo.InvariantCulture.Calendar;

           private static DateTime GetThursdayOfSameWeek(DateTime dt)

           {

               int dow = (int) cal.GetDayOfWeek(dt);

               int numOfDaysToAdd = -3; // The case if day of week is Sunday (dow==0)…

               if (dow != 0) // but if not Sunday…

               {

                   numOfDaysToAdd = 4 – dow;

               }

               return dt.AddDays((double)numOfDaysToAdd);

           }

           public static int GetIso8601WeekOfYear(DateTime dt)

           {

                           

               // Find the thursday

               DateTime thursday = GetThursdayOfSameWeek(dt);

               // Return the week of thursday

               return cal.GetWeekOfYear(thursday, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);            

           }

           // "YYYY–Www–D" Representation

           public static string GetYYYYWwwD(DateTime dt)

           {

               // Find the thursday

               DateTime thursday = GetThursdayOfSameWeek(dt);

               // Return the week of thursday

               int week = cal.GetWeekOfYear(thursday, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);

               // Return the year of thursday

               int year = cal.GetYear(thursday);

               // Return the day of week of original date;            

               int dow = (int) dt.DayOfWeek;

               if(dow == 0) dow=7;

               

               return year.ToString() + "-W" + week.ToString("00") + "-" + dow.ToString();

           }

       }

  8. Scienter says:

    I tested this function and compared it with 2 T-SQL based solutions, and VB6 DatePart using a range comprised of 10k days starting from Jan 1, 2005. What I found are some differences on the following dates with respect to the CLR implementation. It appears that the VB6 Datepart function with a FirstDayofWeek = Monday and FirstWeekofYear = FirstFourDays is closest to the CLR results. However, which one (between VB6 and CLR) is actually correct?

    InputDate  CLR   TSQL1   TSQL2  VB6

    12/31/2007  53   1         1     same

    12/29/2008  53   1         1     1

    12/30/2008  53   1         1     1

    12/31/2008  53   1         1     1

    12/31/2012  53   1         1     1

    12/30/2013  53   1         1     1

    12/31/2013  53   1         1     1

    12/29/2014  53   1         1     1

    12/30/2014  53   1         1     1

    12/31/2014  53   1         1     1

    12/31/2018  53   1         1     1

    12/30/2019  53   1         1     same

    12/31/2019  53   1         1     1

    12/30/2024  53   1         1     1

    12/31/2024  53   1         1     1

    12/29/2025  53   1         1     1

    12/30/2025  53   1         1     1

    12/31/2025  53   1         1     1

    12/31/2029  53   1         1     1

    12/30/2030  53   1         1     1

    12/31/2030  53   1         1     1

    12/29/2031  53   1         1     same

    12/30/2031  53   1         1     1

    12/31/2031  53   1         1     1

    Regards,

    Tristan

  9. Scienter says:

    This is in addition to my earlier post, in case anybody is wondering about the code behind TSQL1 and TSQL2. TSQL1 uses CREATE FUNCTION example A from SQL2000 Books online. TSQL2 uses code from a posting by Andrea Montanari, SQL Server MVP located here. http://groups.google.com/group/microsoft.public.sqlserver.msde/browse_thread/thread/4fd0f517628fc569/24139bb3cb9ee0be?lnk=st&q=&rnum=9#24139bb3cb9ee0be

  10. Scienter says:

    I also found another VB6 article (http://support.microsoft.com/kb/q200299/) describing this. I have since modified my VB6 code to use the functions from this knowledgebase article. Lo and behold, it would now appear that the SQL CLR implementation does not have the correct values. TSQL1 and TSQL2 implementations now match the values reported by the VB6 code. I also mistakenly referenced the wrong link for TSQL2. The correct link is: http://www.sqlservercentral.com/columnists/chedgate/onthetrailoftheisoweek.asp Is this a bug in the CLR?

  11. Isaac Kunen says:

    Interesting detective work.  I’m no expert in this, but it does look like a bug to me.  Just looking at the calendar, the result for 12/31/2007 looks like it should be 1, not 53.