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

Microsoft Data Platform MVP from the Netherlands

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

You can read this blog post at this link: https://jorgklein.com/2008/06/21/ssis-convert-various-string-date-formats-to-datetime-with-the-script-task

This blog has moved to www.jorgklein.com There will be no further posts on sqlblog.com. Please update your feeds accordingly.

You can follow me on twitter: http://twitter.com/jorg__klein

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

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
 

Chris said:

Also curious if you have the method in C#! Thanks!

March 26, 2015 11:33 AM

Leave a Comment

(required) 
(required) 
Submit

About jorg

Jorg Klein, Microsoft Data Platform MVP from the Netherlands.
Privacy Statement