次の方法で共有

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

  • Anonymous
    November 13, 2005
    Is this function (WeekInYear) available in C# or its just in VB.NET?
  • Anonymous
    November 14, 2005
    The comment has been removed
  • Anonymous
    December 21, 2005
    Is there any reason why you can't just use SET DATEFIRST, or am I missing something?
  • Anonymous
    January 09, 2006
    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]
  • Anonymous
    January 09, 2006
    "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.
  • Anonymous
    February 05, 2006
    The other day, colleague Shawn Steele posted in his blog about the ISO 8601 Week of Year format in Microsoft...
  • Anonymous
    May 21, 2006
    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();
           }
       }
  • Anonymous
    July 20, 2006
    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
  • Anonymous
    July 20, 2006
    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
  • Anonymous
    July 20, 2006
    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?
  • Anonymous
    July 21, 2006
    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.