locked
Changing a Date function from VBA to SQL RRS feed

  • Question

  • Hi All

    I am currently in process of migrating our reporting System from MS Access to SSMS. Over the years I have built functions in VBA and I would like to start transferring these Functions to SQL. Could anyone please help me with converting the following function to T-SQL from VBA.
    The purpose of the function is to take all SAP date formatting into consideration and converting it into a standard date type that everyone can use. 
    Therefore, could you please help me convert the following function into a scalar USD function in SQL.

    Function mDat(myDate As Variant) As Date
        Dim A As String
        Dim B As String
        Dim C As String
        Dim y As String
        Dim m As String
        Dim mStr As String
        Dim d As String
        Dim P1 As Integer
        Dim p2 As Integer
        Dim counter As Integer
        Dim mChar As String
        Dim stdDate As String

        If IsNull(myDate) Then myDate = "01/01/1990"
        If Len(myDate) < 8 Then myDate = "01/01/1990"
        If Len(myDate) > 10 Then myDate = Left(myDate, 10)

        --p1 and p2 are the positions of the separators
        P1 = 0
        p2 = 0
        For counter = 1 To Len(myDate)
            mChar = Mid(myDate, counter, 1)
     --the separators can be anything that is not numeric for example _,.:/\|
            If Not IsNumeric(mChar) Then
            If P1 = 0 Then
                P1 = counter
            Else
                If p2 = 0 Then
                    p2 = counter
                Else
                    myDate = Left(myDate, counter - 1)
                End If
                       
            End If
        End If
    Next counter

    If P1 <> 0 Then     --has got separators like: 03-05-2017

        A = Mid(myDate, 1, P1 - 1)
        B = Mid(myDate, P1 + 1, p2 - P1 - 1)
        C = Mid(myDate, p2 + 1, Len(myDate) - p2)
     --mark the first group of numbers A, the middle group B and the last group C
        
        If Len(A) = 4 Then
        
     --the year will be 4 characters and it will be either in the first or last position
            y = A
            m = B
            d = C
        Else
            y = C
            m = B
            d = A
        End If
    End If

    If P1 = 0 Then              -- has no separators like 01052017
        mChar = Mid(myDate, 5, 2)
        If mChar >= "19" Then 'format ddmmyyyy
            d = Left(myDate, 2)
            m = Mid(myDate, 3, 2)
            y = Right(myDate, 4)
        Else                         --format yyyymmdd
            y = Left(myDate, 4)
            m = Mid(myDate, 5, 2)
            d = Right(myDate, 2)
        End If
    End If

    If Val(m) = 1 Then mStr = "Jan"
    If Val(m) = 2 Then mStr = "Feb"
    If Val(m) = 3 Then mStr = "Mar"
    If Val(m) = 4 Then mStr = "Apr"
    If Val(m) = 5 Then mStr = "may"
    If Val(m) = 6 Then mStr = "Jun"
    If Val(m) = 7 Then mStr = "Jul"
    If Val(m) = 8 Then mStr = "Aug"
    If Val(m) = 9 Then mStr = "Sep"
    If Val(m) = 10 Then mStr = "Oct"
    If Val(m) = 11 Then mStr = "Nov"
    If Val(m) = 12 Then mStr = "dec"

    stdDate = d & "-" & mStr & "-" & y

    --this is error resolution
    If Len(stdDate) < 4 Then stdDate = "01-Jan-1990"
    If d > "31" Then stdDate = "01-Jan-1990"
    If d < "01" Then stdDate = "01-Jan-1990"
    If m < "01" Then stdDate = "01-Jan-1990"
    If m > "12" Then stdDate = "01-Jan-1990"
    If y > "9999" Then stdDate = "01-Jan-1990"
    If y < "1900" Then stdDate = "01-Jan-1990"
           
    mDat = DateValue(stdDate)

    End Function

    Your Help will be greatly appreciated.

    Regards

    Jason Orpen

    Wednesday, November 13, 2019 12:02 PM