c# - Convert month name to month number in SSIS

Question

I have an input column "MonthName" which has values in the following string format 22-MAY-2017 02:29:33.00. I would like to convert this into Datetime data type in the destination table.

For that the following conversion needs to be done 22-MAY-2017 02:29:33.00 to 22-05-2017 02:29:33.00

How do i achieve this in Derived Column task.
I am using below expression to fetch the month name part of the value but i don't think it servers much of my purpose

SUBSTRING(MonthName,FINDSTRING(MonthName,"-",1) + 1,FINDSTRING(MonthName,"-",2) - FINDSTRING(MonthName,"-",1) - 1) <br/>

the above expression creates a new column with all the month names ex: may, june, july.

Answer 1

Using Derived Column Transformation

You can use the following expression

LEFT([MonthName],3) + 
(SUBSTRING( [MonthName],4,3) == "JAN" ? "01" :
SUBSTRING( [MonthName],4,3) == "FEB" ? "02" : 
SUBSTRING( [MonthName],4,3) == "MAR" ? "03" :
SUBSTRING( [MonthName],4,3) == "APR" ? "04" :
SUBSTRING( [MonthName],4,3) == "MAY" ? "05" : 
SUBSTRING( [MonthName],4,3) == "JUN" ? "06" :
SUBSTRING( [MonthName],4,3) == "JUL" ? "07" :
SUBSTRING( [MonthName],4,3) == "AUG" ? "08" :
SUBSTRING( [MonthName],4,3) == "SEP" ? "09" : 
SUBSTRING( [MonthName],4,3) == "OCT" ? "10" : 
SUBSTRING( [MonthName],4,3) == "NOV" ? "11" : 
SUBSTRING( [MonthName],4,3) == "DEC"? "12":"") 
+ RIGHT([MonthName],17)

Using Script Component

If the Date column is a string you can use the DateTime.ParseExact method in a script component. (assuming that outDate is the output column and inDate is the input column)

using System;
using System.Globalization;


CultureInfo provider = CultureInfo.InvariantCulture;

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    Row.outDate = DateTime.ParseExact(Row.inDate,"dd-MMM-yyyy HH:mm:ss.ff",provider).ToString("dd-MM-yyyy HH:mm:ss.ff");
}

for more info on this method you can refer to this links:

• DateTime.ParseExact Method
• C# DateTime.Parse

Also take a look a my answer in the following link, it is very helpful:

• SSIS Source Format Implicit Conversion for Datetime