Converting Between Calendar And Working Days In VB.NET

With all the fun of creating functionality similar to this in SQL, made some optimizations within original VB.NET functions for converting between calendar and working/business days and got to thinking it would be good to start taking advantage of the fact that SQL 2005 supports CLR assemblies. Instead of having to do overly complex SQL functions/queries/procedures, use SQL CLR as it will make your life much easier and you will look cool doing it.

Furthermore, is too easy not to. To convert my utility class already containing public shared functions for dealing with going back and forth between working and calendar days, I simply added the two imports shown in the code listing below; Partial identifier to my class; <SqlFunction()> to each method I want exposed to CLR. Even if I went the next step to convet to SqlTypes defined in the first import, that is not too difficult.

Well let's dive into the code listing:

Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class MyFirstUDFn
    ''' <summary>
    ''' Get calendar days from today equivalent to working days specified.
    ''' </summary>
    ''' <param name="wrkDays"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    <SqlFunction()> _
    Public Shared Function CalendarDaysFromWorkDays(ByVal wrkDays As Integer) As Integer

        'Using integer division, get the whole number of work weeks 
        Dim calDays As Integer = (wrkDays \ 5) * 7
        calDays += (wrkDays Mod 5) 'Add back the remaining work days

        'Adjust result to fall on working day as originally intended
        While IsWeekend(DateAdd(DateInterval.Day, calDays, Date.Now()))
            calDays += Math.Sign(wrkDays)
        End While

        Return calDays
    End Function

    'Create list of weekend days - for US this is Saturday (7) and Sunday (1)
    Private Shared ReadOnly WEEKEND_DAYS As New List(Of Integer)(New Integer() {7, 1})
    ''' <summary>
    ''' Returns flag indicating if date is a weekend day or not.
    ''' </summary>
    ''' <param name="calDate"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    <SqlFunction()> _
    Public Shared Function IsWeekend(ByVal calDate As DateTime) As Boolean
        Return WEEKEND_DAYS.Contains(Weekday(calDate))
    End Function

    ''' <summary>
    ''' Get working days from today equivalent to calendar days specified.
    ''' </summary>
    ''' <param name="calDays"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    <SqlFunction()> _
    Public Shared Function WorkDaysFromCalendarDays(ByVal calDays As Integer) As Integer
        Dim wrkDays As Integer = 0

        If calDays > 0 Then
            For i As Integer = 1 To calDays
                If Not IsWeekend(DateAdd(DateInterval.Day, i, Date.Now())) Then
                    wrkDays += 1
                End If
            Next
        ElseIf calDays < 0 Then
            For i As Integer = calDays To (-1)
                If Not IsWeekend(DateAdd(DateInterval.Day, i, Date.Now())) Then
                    wrkDays -= 1
                End If
            Next
        End If

        Return wrkDays
    End Function
End Class

As you have noticed by now, the function logic look incredibly similar to the code we explored in our SQL adventure. As stated, the SQL code originated from the CalendarDaysFromWorkDays function and its counterpart; however, the code for WorkDaysFromCalendarDays is more like our SQL solution that its original just simplier form since these functions are used for a set number of days from current date versus a date range; however, the principles are the same.

One of the additions, is extending this to work with different weekend days and the bonus IsWeekend function that came as a result of implementing that. We could easily extend this to query for holidays and have a function for IsHoliday as well. See the reference to Using conn As New SqlConnection("context connection=true") within the Microsoft Solutions Developer Network (MSDN) link below.

Anyway, where do we go from here. Well the simple step is to create a SQL Server Project! For those of you using Visual Studio 2005/2008 Express as I am on one machine, you are probably asking yourself "What SQL Server Project?". Exactly. This is available in the full version of Visual Studio, so here is what I did to develop this in VS Express.

Create a new Class Library project in VS and ensure that you go into the properties of the project and remove the root namespace (you can leave this as-is or change to another namespace to have structured classes, but just take note of that for later as using methods within assemblies are of the format AssemblyName.ClassName.MethodName so if you have a class in a long namespace it must be declared like AssemblyName.[NamespaceName.ClassName].MethodName). Add a class item to your project and code away. Above code functions as-is and is a good example of all that is needed to make a normal VB function to SqlFunction or sub to SqlStoredProcedure.

Once compiled to a DLL, the do the following on the SQL Server:

sp_configure N'clr enabled', 1
go
reconfigure
go
CREATE ASSEMBLY MyAssembly FROM 'C:\SQLCLRProject.dll' WITH PERMISSION_SET = SAFE

Once you have the assembly created and CLR enabled, you can issue this SQL to test:

-- creates the function
CREATE FUNCTION udf_IsWeekend(@calDate datetime) RETURNS bit
AS
EXTERNAL NAME SQLUserDefinedFunctions.MyFirstUDFn.IsWeekend
GO

-- example usage
SELECT dbo.udf_IsWeekend(DateAdd(dd, 2, getdate()))

Now you are using your original .NET code in SQL server and no need to pull your here out finding SQL equivalents to pre-existing .NET functions. Although, that is fun in and of itself, sometimes you just need to save time and practicing code resuse is always a good thing.

Hope that helps!


Related Articles/References:

• Using SQL To Find Work Days In Date Range
• Converting Working Days To Calendar Days Using SQL
• Using CLR Integration in SQL Server 2005: http://msdn.microsoft.com/en-us/library/ms345136.aspx
• SQL Server DBA Guide to SQLCLR: http://www.sqlskills.com/resources/Whitepapers/SQL%20Server%20DBA%20Guide%20to%20SQLCLR.htm