1

Destination column data should in one format:

enter image description here

How to do the HireDate column in one format at destination table of sql server.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Ajit Kumar
  • 65
  • 1
  • 1
  • 3
  • Your source formats are ambiguous. Is `01/10/2008` 1st Oct or 10th Jan? You don't know so you can't convert it. Also *forget* about date 'formats' in SQL Server. Choose the correct datatype `DATE` and forget about visual format – Nick.Mc Feb 27 '17 at 05:20
  • Unless you have some way to determin the date format used in each record in the flat file, it can't be done correctly. As @Nick.McDermaid wrote - all dates where the day is less then 13 will be ambiguous. Also, you should know that [datetime data type does not save display format.](http://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) – Zohar Peled Feb 27 '17 at 07:18
  • If it have only two types of formats i.e. yyyy/mm/dd or dd/mm/yyyy than can we change to yyyy/mm/dd format? @Nick.McDermaid – Ajit Kumar Feb 27 '17 at 09:15
  • But how do you know which row has which format? How do you know Sumit is `dd/mm/yyyy` and not `mm/dd/yyyy`? – Nick.Mc Feb 27 '17 at 09:18
  • Once you have established that you need to try something yourself and post your attempt. – Nick.Mc Feb 27 '17 at 09:19

1 Answers1

0

Add a script component , Mark HireDate as input column, add a new Output column outHireDate with dataType DT_DBTIMESTAMP

In the script write the following:

Declare an array of formats:

Dim strFormats() as string = {"dd/MM/yyyy","yyyy/MM/dd","MM/dd/yyyy"}

And in Input0_ProcessInputRow sub use DateTime.ParseExact Function as follow:

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

Important note

It may cause conflict when day and month are lower than 13 (ex: 01/03/2010)

Hadi
  • 36,233
  • 13
  • 65
  • 124