Creating more SQL Server 2005 date functions

TSQL is great, but there's a couple of date-related functions I almost always miss when I'm working with it, at least if I'm doing something related to reporting and BI. Many things need to be displayed per week or quarter, and especially weeks is something that SQL Server isn't very good at. DATEPART doesn't work correctly outside of the US when it comes to week/ww/wk -parts, because it doesn't support ISO 8601 correctly. Years ago I implemented a correct week function as a stored procedure, but now I wanted to do the same in C# in a user defined function and run it through SQL CRL integration.

The first problem I ran into was that the .net Framework claims to implement ISO 8601, but doesn't. Doh! I blogged about this in my last post, "GetWeekOfYear is not reliable in .NET". But with the help of a good post by Shawn Steele I was able to implement a working version.

Creating a user defined function in SQL Server 2005

In Visual Studio there's a project template called "SQL Server Project" that let's you create user defined functions among other things.

Once you've created a project based on that template (and specified a database connection) you can right-click on the project node in the Solution Explorer and select "Add" and then "User Defined Function...". Give it a filename (for example "MyDateFunctions.cs"), and click "Add". This gives you a sample function that in at least Visual Studio 2008 looks like this:

   1: using System;

   2: using System.Data;

   3: using System.Data.SqlClient;

   4: using System.Data.SqlTypes;

   5: using Microsoft.SqlServer.Server;

   6:  

   7: public partial class UserDefinedFunctions

   8: {

   9:     [Microsoft.SqlServer.Server.SqlFunction]

  10:     public static SqlString Function1()

  11:     {

  12:         // Put your code here

  13:         return new SqlString("Hello");

  14:     }

  15: };

If you now right-click on the project node in the Solution Explorer and click "Deploy" the code will be compiled, and the function will be deployed to the SQL Server and the database you specified in the database connection you provided when you created the project. If you then open the SQL Server Management Studio (or SQL Server Management Studio Express if you're using that) you can call the function in a query, for example like this: SELECT dbo.Function1()

   1: SELECT dbo.Function1()

Which should return "Hello".

Now, to do something more productive with this, here's the code for two date-related functions:

   1: using System;

   2: using System.Data;

   3: using System.Data.SqlClient;

   4: using System.Data.SqlTypes;

   5: using Microsoft.SqlServer.Server;

   6: using System.Globalization;

   7:  

   8: public partial class UserDefinedFunctions

   9: {

  10:     [Microsoft.SqlServer.Server.SqlFunction]

  11:     public static SqlInt32 Week(SqlDateTime date)

  12:     {

  13:         return new SqlInt32(GetIso8601WeekOfYear(date.Value));

  14:     }

  15:  

  16:     [Microsoft.SqlServer.Server.SqlFunction]

  17:     public static SqlString YearWeek(SqlDateTime date)

  18:     {

  19:         int weekNumber = GetIso8601WeekOfYear(date.Value);

  20:  

  21:         int year = date.Value.Year;

  22:  

  23:         if (weekNumber >= 53 && date.Value.Month == 1)

  24:             year -= 1;

  25:  

  26:         if (weekNumber == 1 && date.Value.Month == 12)

  27:             year += 1;

  28:  

  29:         string yearWeek = year.ToString() + '-' + string.Format("{0:D2}" ,weekNumber);

  30:  

  31:         return new SqlString(yearWeek);

  32:     }

  33:  

  34:  

  35:     // The code below was taken from Shawn Steele's blog post:

  36:     // http://blogs.msdn.com/shawnste/archive/2006/01/24/iso-8601-week-of-year-format-in-microsoft-net.aspx

  37:  

  38:     // This presumes that weeks start with Monday.

  39:     // Week 1 is the 1st week of the year with a Thursday in it.

  40:     private static int GetIso8601WeekOfYear(DateTime time)

  41:     {

  42:         // Need a calendar.  Culture's irrelevent since we specify start day of week

  43:         Calendar cal = CultureInfo.InvariantCulture.Calendar;

  44:  

  45:         // Seriously cheat.  If its Monday, Tuesday or Wednesday, then it'll 

  46:         // be the same week# as whatever Thursday, Friday or Saturday are,

  47:         // and we always get those right

  48:         DayOfWeek day = cal.GetDayOfWeek(time);

  49:         if (day >= DayOfWeek.Monday && day <= DayOfWeek.Wednesday)

  50:         {

  51:             time = time.AddDays(3);

  52:         }

  53:  

  54:         // Return the week of our adjusted day

  55:         return cal.GetWeekOfYear(time, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);

  56:     }

  57: };

The first function, Week, returns the week number for a give date. The second function, YearWeek, returns a string in the format of CCYY-WW, for example "2007-51" for today. This is quite convenient when creating reports and graphs where stuff is grouped per week, because sorting and grouping on such a value is easy.

Once the code above is deployed to the server you can run queries like these:

   1: SELECT dbo.Week('2007-12-31')

   2: SELECT dbo.YearWeek('2007-12-22')

   3: SELECT dbo.YearWeek('2008-2-14')

   4: SELECT dbo.YearWeek('2007-12-31')

   5: SELECT dbo.YearWeek('2008-12-31')

   6: SELECT dbo.YearWeek('2009-12-31')

Which should give you the following result:

   1: 1

   2: 2007-51

   3: 2008-07

   4: 2008-01

   5: 2009-01

   6: 2009-53

So as you can see it gives the right week numbers for the last days of 2007, 2008 and 2009, which DATEPART can't do. In addition, if you use YearWeek, you get a nicely formatted string that you can use in reports and such.

Comments

[Christian Lindholm]

This post proved to be useful, even though i did not need the information for a SQL function but a WinForms application.
I did however find a small bug in your code:

SELECT dbo.YearWeek('2006-01-01') incorrectly returns 2006-52 (it should return 2005-52). This is easily corrected by a small change on row 23 in your code.
The new row 23:
if (weekNumber >= 52 && date.Value.Month == 1)

With the correction 2006-01-01 correctly belongs to 2005-52.