Is there a decent technique for SSIS Derived Column date formatting?

Question

I just had to write the most redonkulous expression in an SSIS Derived Column to get dates formatted like "01-JAN-2010".  It lookes like this:

      

There's got to be a better way... isn't there?

Answer 1

Consider using a script component and then Row.stringcol = Row.datecol.ToString("dd-MMM-yyyy") to populate a new string column in the data flow.

Answer 2

If you want to use expressions, you could use this instead:

RIGHT("0" + (DT_WSTR, 2)DAY(ASSIGNMENT_BEGIN_DATE), 2) + "-" + SUBSTRING("JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC", (3 * MONTH(ASSIGNMENT_BEGIN_DATE)) - 2, 3) + (DT_WSTR, 4)YEAR(ASSIGNMENT_BEGIN_DATE)

Didn't say it was better. It's just different and shorter. Use a Script as Cade suggested if you want better control.

Answer 3

Okay, now I feel like a Donkey. The easy solution is to make my Data Source a select statement in which I simply handle that column like this:

     SELECT to_char(assignment_begin_date, 'dd-MON-yyyy') assignment_begin_date FROM ...

The RDBMS is Oracle, btw.

Then I don't need to do a derived column or a script.

I have self-administered a dope-slap for not thinking of it earlier. Thanks to all for the help.