H.Fadlallah H.Fadlallah - 9 months ago 71
Vb.net Question

SSIS convert string date with various formats to DateTime

I am using ssis to Import Text Files (exported from many source) Containing Invoices Tables into SQL Server

there is a column named

with different formating (each source may have different format)


2016-01-01 00:00:00
01/01/2016 00:02:00
01-Jan-2016 10:00:01
20160101 10:00:00

is there a way to convert dates to Datetime column (using VB.net script) whatever the format?

Answer Source

Declare an array of formats:

Dim strFormats() as string = {"dd/MM/yyyy HH:mm:ss","yyyy-MM-dd HH:mm:ss","yyyyMMdd HH:mm:ss","dd-MMM-yyyy HH:mm:ss","MM/dd/yyyy HH:mm:ss"}

And in Row processing (each row process) use DateTime.ParseExact Function as follow:

Row.OutDate = DateTime.ParseExact(Row.InvoiceDate,strFormats,New System.Globalization.CultureInfo("en-GB"))