September 23, 2008 at 11:28 am
I am grabbing a substring and am trying to do a type cast to a date as follows...
(DT_DATE)SUBSTRING([Column 0],247,8)
The strings I am isolating in my substring look like this... 07312007 with some nulls as well
It keeps kicking it out every row to my redirect trash destination. Is there a setting I am missing on this? It says in the MSDN documentation that this type conversion should be allowed but do I need to massage the string some more before it can do some sort of implicit conversion to a date (i.e. from ddmmyyyy to yyyymmdd)?
September 23, 2008 at 3:57 pm
The following seemed to have worked...
(LEN(TRIM((SUBSTRING([Column 0],247,8)))) < 1 ? "" : (SUBSTRING([Column 0],247,2) + "/" + SUBSTRING([Column 0],249,2) + "/" + SUBSTRING([Column 0],251,4)))
by reformatting the string to the dd/mm/yyyy format and substituting in an empty string where there is no date to be found. I type cast the subsequent string in a data conversion object after using a conditional split to filter out the empty strings. The converted data types were then joined in a union all with the empty string records. now I need to write to the database table instead of and trash destination and will write a follow on post on how that went tomorrow. 😉
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply