How to change a Date Column into two separate colu

Question

I am importing a spreadsheet that has a date column labeled StartDate (01/01/2000) I need to split it into two columns one that is Month the other that is Year.

The Month has to be the Month Name (Jan, Feb, etc) the other is a simple 4 digit year.

I want to do this through an SSIS package.

I have tried to created a derived column using some date expressions but I can't get anything to work the way I need it to.

Does anyone have any ideas on what is the proper script to do this?

Thank you.

Answer 1

I'm assuming StartDate has a Date-ish data type and has not been imported as a string (DT_STR or DT_WSTR).

Add a Derived Column into your Data Flow. Give it a name, like StartMonth, and use an expression like MONTH([StartDate]).

Add a second Derived Column, call it StartYear and use an expression like YEAR([StartDate])

And since I can't read, you asked for Month names. This gets a little uglier as there isn't a built in Expression functionality for this.

You could write your own using the Ternary Operatior (test) ? true : false but a limitation of this approach is that the expression editor is cumbersome for long expressions.

([StartMonth]==1)?"January":([StartMonth]==2)? "February" : ([StartMonth]==3)? "March" : "Etc"

An alternative to this is to use a Script Task. In the Rows tab, check our StartDate column as a readonly input. In the Output, add a new Column, named StartMonthName as type String length 3 (if you only want the 3 letter abbreviation). And if you're already using a script task, you can eliminate the Derived Column call by adding the StartMonth in from here.

Row.StartMonthName = Row.StartDate.ToString("MMM");
Row.StartMonth = Row.StartDate.Month;