month ('jan','feb',...) string to date conversion fails

I use the derived column to convert a string date from a flat file like this: "Jan 02 2005" into a datetime.
I have seen in the forum to use: (DT_DATE)(SUBSTRING(mydate,5,2) + "-" + SUBSTRING(mydate,1,3) + "-" + SUBSTRING(mydate,8,4))
However, even if it produces a string like '02-Jan-2005', the following cast to dt_date fails.
I have also tried inverting month and day, year/month/day but all with the same result:

Derived Column [73]]

Error: The component "Derived Column" failed because error code

0xC0049064 occurred, and the error row disposition on "output column"...

I think the cast fails bacause of the month format. Therefore the only solution would be to code in in a lookup table Jan, 01 | Feb, 02 |... ?

srem wrote:

I use the derived column to convert a string date from a flat file like this: "Jan 02 2005" into a datetime.
I have seen in the forum to use: (DT_DATE)(SUBSTRING(mydate,5,2) + "-" + SUBSTRING(mydate,1,3) + "-" + SUBSTRING(mydate,8,4))
However, even if it produces a string like '02-Jan-2005', the following cast to dt_date fails.
I have also tried inverting month and day, year/month/day but all with the same result:

Derived Column [73]] Error: The component "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "output column"...

I think the cast fails bacause of the month format. Therefore the only solution would be to code in in a lookup table Jan, 01 | Feb, 02 |... ?

Its not an ideal situation but if you want to achieve this in a single derived column component what you will need to do is build some nested conditional operators. Its a bit of a fraught process to begin with but once you get the hang of it it isn't too bad.

I hope Microsoft give us the ability to extend the expression language in the next version by allowing us to build our own custom expression functions.

-Jamie

|||I suppose you could build a quick lookup table using a simple stored procedure. Then all you'd have to do is join on that "Jan 02 2005" column to get you a real date field in return.

Might even work better than doing all of the CASTs and conditional logic tests.|||

If you know exactly what the month strngs are, and they are all 3 chars long, you could do something like the following to get month number:

(FINDSTRING(month, "JANFEBMARAPR....DEC",1) + 2 ) / 3

If there are multiple options, or the lengths are irregular you could still do something similar, but it might not be worth it anymore over the nested conditionals.