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

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)