Integration Services

SSIS – Convert various String date formats to DateTime with the Script Task

Posted by

The script below is really nice if you need to convert String dates that come in different (unexpected) formats.
Just copy and paste the code in your Script Task (paste the function outside Main()) and it works right away.

Supported date formats:

  • YYMMDD
  • YY-MM-DD
  • YYYYMMDD
  • YYYY-MM-DD

Of course it’s possible to add code for more date formats yourself. If you want to, copy and paste your code in a comment. I will then add the code to this blog.

—————————————————————————————————————————

Public Shared Function GetDateFromString(ByVal stringDate As String) As DateTime

        Dim datetimeResult As DateTime

        Try

            Dim centuryToAdd As Integer = 1900

            If (Convert.ToInt32(stringDate.Substring(0, 2)) < 80) Then

                centuryToAdd = 2000

            End If

            If (stringDate.Length = 6) Then

                ‘Format is: YYMMDD

                datetimeResult = New DateTime((centuryToAdd + Convert.ToInt32(stringDate.Substring(0, 2))), Convert.ToInt32(stringDate.Substring(2, 2)), Convert.ToInt32(stringDate.Substring(4, 2)), 0, 0, 0)

                Return datetimeResult

            End If

            If (stringDate.Length = 8) Then

                If (stringDate.IndexOf(“-“) > 0) Then

                    ‘Format is: YY-MM-DD

                    datetimeResult = New DateTime((centuryToAdd + Convert.ToInt32(stringDate.Substring(0, 2))), Convert.ToInt32(stringDate.Substring(3, 2)), Convert.ToInt32(stringDate.Substring(6, 2)), 0, 0, 0)

                    Return datetimeResult

                End If

                ‘Format is: YYYYMMDD

                datetimeResult = New DateTime(Convert.ToInt32(stringDate.Substring(0, 4)), Convert.ToInt32(stringDate.Substring(4, 2)), Convert.ToInt32(stringDate.Substring(6, 2)), 0, 0, 0)

                Return datetimeResult

            End If

            If (stringDate.Length = 10) Then

                ‘Format is: YYYY-MM-DD

                datetimeResult = New DateTime(Convert.ToInt32(stringDate.Substring(0, 4)), Convert.ToInt32(stringDate.Substring(5, 2)), Convert.ToInt32(stringDate.Substring(8, 2)), 0, 0, 0)

                Return datetimeResult

           End If

            Return Convert.ToDateTime(stringDate)

        Catch e As Exception

        End Try

        ‘No date format found: Return unknown(1/1/1900)

        datetimeResult = New DateTime(1900, 1, 1, 0, 0, 0)

        Return datetimeResult

    End Function

—————————————————————————————————————————

If you want to convert a String SSIS variable and load it into a DateTime SSIS variable, use the following code in your Script Task:

Dts.Variables(“someDateTimeVariable”).Value = GetDateFromString(Dts.Variables(“someStringVariable”).Value.ToString)

 

5 comments

  1. Hi jorg,
    I am new to SSIS and learning how to use script task to convert string to date data type.i am confused with above blog since i am a beginner can you tell me where can i find your blog to convert string to date data type.

    Like

    Reply

  3. Thanks for the hint Paul – I had a similar challenge to Jorg, and after reading your comment, ended up at DateTime.ParseExact.  I think DateTime.Parse wouldn’t work as my input dates are in many weird formats. Jorg’s example would probably have similar problems with DateTime.Parse.
    I’m using the form of DateTime.ParseExact that accepts an array of date formats ( http://msdn.microsoft.com/en-us/library/332de853(VS.90).aspx ).  I prefer to do each conversion in a single line of code (rather than call a function), wrapped in a Try … Catch so that I get Null dates output if all the formats fail.  E.g. (in Sub Input0_ProcessInputRow):
           Dim dateFormats() As String = {“dd/MM/yyyy”, _
                                  “dd/MM/yy”, _
                                  “yyMMdd”, _
                                  “yy-MM-dd”, _
                                  “yyyyMMdd”, _
                                  “yyyy-MM-dd”, _
                                  “yyyy0000”}
           Try
               Row.MarriageDate = DateTime.ParseExact(Row.MARRIAGEDATERET, dateFormats, Nothing, Globalization.DateTimeStyles.None)
           Catch ex As Exception
           End Try
    (repeat the Try … Catch block for other columns).
    Mike

    Like

    Reply

Leave a comment