String to Value Algorithm

Well continuing on the thought of conversion routines, here is an example of taking a string value in Transact-SQL on Microsoft SQL Server (may work on other platforms that support same functions) and creating a unique integer value.

Code listing:

DECLARE @text nvarchar(100), @value bigint
SET @text = 'Smith'
SET @value = 0
WHILE LEN(@text) > 0
BEGIN
SET @value = @value + ASCII(LEFT(@text, 1)) * square(LEN(@text))
SET @text = RIGHT(@text, LEN(@text) - 1)
END
SELECT @value

Very simple hopefully. The basis is using the length of the string, create a large integer value that is multiplied against the ascii value of each letter in the string. I was asked this as a question, so I have not explored all of the uses of a function like this; however, as a quick hash it seems to work fine.

Again, just posting for the learning of some of the tools available in SQL like the ASCII and SQUARE functions. Hopefully making the life of some other DBA or programmer a little easier.


References:

• BigInt: http://msdn.microsoft.com/en-us/library/ms187745(SQL.90).aspx
• Numeric: http://msdn.microsoft.com/en-us/library/ms187746(SQL.90).aspx
• ASCII: http://msdn.microsoft.com/en-us/library/ms177545.aspx
• Square: http://msdn.microsoft.com/en-us/library/ms173569.aspx

Converting Between String, ASCII, And Hex In .NET

Welcome again to my twisted mind. This post is just a quick example on converting between string, ascii and hexadecimal values using the .NET, specifically visual basic .NET programming.

Dim strOrig As String = "Hello"
Dim decimals As New List(Of Decimal)()
Dim hexStrs As New List(Of String)()
Dim intCurrent As Double

For Each c As Char In strOrig.ToCharArray()
    intCurrent = Strings.Asc(c)
    decimals.Add(intCurrent)
    hexStrs.Add(Conversion.Hex(intCurrent))
Next

If hexStrs.Count > 0 Then
    Console.Write("Hex: " & hexStrs(0))

    For i As Integer = 1 To (hexStrs.Count - 1)
        Console.Write(" " & hexStrs(i))
    Next

    Console.WriteLine("")
End If

If decimals.Count > 0 Then
    Console.Write("Decimals: " & decimals(0))

    For i As Integer = 1 To (decimals.Count - 1)
        Console.Write(" " & decimals(i))
    Next

    Console.WriteLine("")
End If

Console.ReadLine()

As you can see above, .NET provides some nice conversion utilities for ascii value of a character and converting a decimal value to hexidecimal making the solution very straightforward as we just need to create a character array from string value and then convert each character to its ascii equivalent. Furthermore, we can quickly get the hexidecimal value for the character.


This is not rocket science by any stretch, but like posting things like this even as simple as it is so I for one don't forget it and to help another programmer get started on something more complex.


Anyway, for now that is it.

Happy programming.

References:

• How to convert string to hexadecimal and vice versa (C#)
• Conversion.Hex: http://msdn.microsoft.com/en-us/library/f480a85s.aspx
• Strings.Asc: http://msdn.microsoft.com/en-us/library/78sb8546.aspx

Using SQL To Find Work Days In Date Range II

In Using SQL To Find Work Days In Date Range, we created our fn_GetWorkDaysInRange user defined function in Microsoft SQL Server 2005. However, through our research into VB.NET and other simpler algorithms if you have been reading along, if we can do it better why not learn how.

So not to leave well enough alone, here is the code listing our fn_GetWorkDaysInRange revisited:

ALTER FUNCTION [dbo].[fn_GetWorkDaysInRange]
(
    @startDate datetime    -- first datetime in range
    , @endDate datetime -- last datetime in range (can be in past)
    , @includeStartDate bit -- flag to include start date as a work day
    , @firstWkndDay int -- first day of weekend (e.g. Day(datetime))
    , @lastWkndDay int -- last day of weekend (e.g. Day(datetime))
)
RETURNS int
AS
BEGIN
    -- variables used in processing
    DECLARE @workDays int, @sign int

    -- parse input and calculate direction of date range
    SET @firstWkndDay = Coalesce(@firstWkndDay, 0)
    SET @lastWkndDay = Coalesce(@lastWkndDay, @firstWkndDay)
    SET @startDate = Coalesce(@startDate, getdate())
    SET @endDate = Coalesce(@endDate, @startDate)
    SET @sign = Sign(DateDiff(dd, @startdate, @enddate))
   
    -- set initial value of work days result based on include start date value
    -- we use sign so that end dates older than start return negative work days
    -- for work days to come up as positive value no matter what, sign usage can be replace by 1
    SET @workDays = CASE @includeStartDate WHEN 0 THEN 0 ELSE
        CASE
            WHEN DatePart(dw, @startDate) IN (@firstWkndDay, @lastWkndDay)
            THEN 0
            ELSE Case @sign When 0 Then 1 Else @sign End
        END
    END
   
    -- while end date is not equal to start date add sign (-1/1) number of days
    -- and add to work days total if not a weekend
    WHILE DateDiff(dd, @startDate, @endDate) <> 0
        BEGIN
        SET @startDate = DateAdd(dd, @sign, @startDate)
        SET @workDays = @workDays +
            CASE
                WHEN DatePart(dw, @startDate) IN (@firstWkndDay, @lastWkndDay)
                THEN 0
                ELSE @sign
            END
        END

    -- return working days result to caller
    RETURN @workDays
END

The number of lines look very similar, but if you closely inspect this new version there are many changes/simplifications. The table variable is no longer needed, removing further dependence on new version(s) of SQL. The logic is reduced to while loop with two execution lines: increment/decrement date value; add to work days total if the new date value is a working day. The extra code is for readability of case logic.

To have this reflect how clean it really is, we can abstract out the logic for weekend which gets rid of the parameters and set statements for weekend day along with extensive case logic. We could then extend that separate function named something like fn_IsNotWeekDay to include logic to check date against our holiday table returning a bit flagging weekend/holiday. With CLR based user defined functions like we explored using VB.NET, this logic can be as complex as we are capable of coding in either SQL or .NET.

Until the next learning adventure.

Keep the code alive!


Related Articles/References:

• Using SQL To Find Work Days In Date Range
• Converting Working Days To Calendar Days Using SQL
• Converting Between Calendar And Working Days In VB.NET

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

Converting Working Days To Calendar Days Using SQL

In the Using SQL To Find Work Days In Date Range post I mentioned that I original thought it would be an easy conversion from calendar days to work days as using simple mathematical formula in SQL, you can get calendar days or a calendar date from working days. It was my thought that the opposite of this formula would work; however, that is another post you can read at your leisure. In this post, I figured I would share my simply formula. It is so straight forward that hopefully it will save you from wasting time going for a more complex approach.

Here is the code listing:

DECLARE @workDays int, @calDays int
SET @workDays = 3

-- using integer division to convert work weeks to calendar weeks
-- AND modulus division to get partial week's days
SELECT @calDays = @workDays / 5 * 7 + @workDays % 5
-- just double check that end result is not on a weekend
WHILE DatePart(dw, DateAdd(dd, @calDays, getdate())) IN (7, 1)
SET @calDays = @calDays + 1
-- select away you have your calendar days and date if you would like
SELECT DateAdd(dd, @calDays, getdate())

As I said, pretty straight forward. Since SQL, at least in Microsoft SQL Server, will treat division of int datatypes as integer division, you will get the whole number amount of weeks represented by work days (i.e. 8/5 = 1). For other platforms, just use FLOOR function or its equivalent in your code (e.g. Floor(@workDays / 5) or the integer division operator to achieve the same results. Subsequently, modulus is taken of working days to get the partial week days involved which should be 0 - 4 that we can add to calendar days we calculated from multiplying the number of weeks by 7.

You can just run in query window as above, but to complement our function/procedure to get working days from a set of calendar dates you can put this in a stored procedure or user defined function the returns the calendar days themselves or the resulting date.

Keep evolving development!