Change month name to month number in SSIS

Question

I have a source file which has 50 columns. One of the columns is TransDateTime and the value in it is of format 22-MAY-2017 02:31:15.00. Now before loading this source file to final destination table i want it to be converted to the format 22-05-2017 02:31:15.00 so that i can use the data type datetime for that column.

I have seen ways to convert month name to month number if the column just contains the month value like below.

• http://www.techbrothersit.com/2014/11/ssis-how-to-convert-month-name-into.html

I am not sure how to work around this scenario in my case. Any solutions?

Answer 1

Well one solution would be to REPLACE. Simply replace "MAY" with "05" and you get the desired outcome from your sample input.

Nest 12 REPLACE functions in one expression and you will handle every possible scenario.

Answer 2

Use a script component. Convert the input column to your desired format like this in C#:

DateTime.Parse("22-MAY-2017 02:31:15.00").ToString("dd-MM-yyyy HH:mm:ss.ff");

For example, if your input column is MyDate and Out put column is OutPutCol then:

OutPutCol = DateTime.Parse(Row.MyDate).ToString("dd-MM-yyyy HH:mm:ss.ff");

You can check the code here.