THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jorg Klein

Jorg Klein, Microsoft-only BI consultant from the Netherlands

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)

 

Published Thursday, June 12, 2008 11:44 PM by jorg
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

kim said:

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.

July 27, 2010 9:50 AM
 

Paul said:

Sweet mother of God, have you heard of Date.Parse?

August 18, 2010 9:03 PM
 

Mike Honey said:

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

January 30, 2011 7:32 PM
 

Kamal said:

Hi Jorg,

Nice Post :) .. was struggling with this issue. Do you have the above method written in C# by any chance?

July 6, 2011 9:43 PM

Leave a Comment

(required) 
(required) 
Submit

About jorg

Jorg Klein, Microsoft-only BI consultant from the Netherlands, blogging about BI on SQL Server with a focus on SSIS.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement