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