SSIS Convert numeric to datetime

Question

I'm trying to convert a numeric column with the following defined format: DDMMYYYY to a datetime in a mssql database.

What I tried is to do this with a "derive column".

My idea was to get the year, month and day part as strings, concatenate them and convert them to a DT_DBTIMESTAMP with the following code:

(DT_DBTIMESTAMP)(SUBSTRING((DT_WSTR,8)[field],5,4) + "-" + SUBSTRING((DT_WSTR,8)[field],3,2) + "-" + SUBSTRING((DT_WSTR,8)[field],1,2))

I also tried to do it with DT_DBDATE but it didn't work either.

The column of my table looks like this: [GebDat] datetime

Do you have any idea how I can achieve this result? Or maybe give me a hint, what I was doing wrong in the above approach?

Thanks in advance

What does the output of your transform look like? And does your database expect a date to be YYYY-MM-DD? — Andrew
– Andrew, Commented Dec 6, 2013 at 15:48
Hey Andrew I'm not exactly sure what you mean by output.. in the progress windows I get following message: [Derive Columns Births [397]] Error: An error occurred while attempting to perform a type cast. The cast-error happens when casting to (DT_DBTIMESTAMP) so the cast of the field to DT_WSTR works... And yes, the database expects a date to be YYYY-MM-DD — xeraphim
– xeraphim, Commented Dec 6, 2013 at 15:57
Do you get the same error when converting to DT_DBDATE? At a guess, you've got some values that cannot be converted correctly to a date. — Andrew
– Andrew, Commented Dec 6, 2013 at 16:03
Also, Try to redirect rows that fail to some sort of error output, so you can see what they look like. BTW, your code looks good to me. — Andrew
– Andrew, Commented Dec 6, 2013 at 16:09
You were absolutely right with your guess, that we have some values that are "corrupt". I added an answer where I'm describing what happens more accurately. Thank you very much for leading me on the right path :-) — xeraphim
– xeraphim, Commented Dec 6, 2013 at 16:20

xeraphim · Accepted Answer · 2013-12-06 16:25:14Z

The code in my initial question was actually correct.

But what Andrew suggested, that there are some values that cannot be converted correctly, was absolutly right. We had some values that didn't conform to the spec (for example a numeric value 4012012 instead of 04012012 which lead to the SSIS trying to convert 40-12-012 to a date, what obviously fails.

What I'm now doing is to check for the length of the input param first. If it is 8 then do exactly the code in the question, if it is 7 then add a 0 to the day and get the day, month, year accordingly:

LEN((DT_WSTR,8)[field]) == 7 ? (DT_DBTIMESTAMP)(SUBSTRING((DT_WSTR,8)[field],4,4) + "-" + SUBSTRING((DT_WSTR,8)[field],2,2) + "-0" + SUBSTRING((DT_WSTR,8)[field],1,1)) : (DT_DBTIMESTAMP)(SUBSTRING((DT_WSTR,8)[field],5,4) + "-" + SUBSTRING((DT_WSTR,8)[field],3,2) + "-" + SUBSTRING((DT_WSTR,8)[field],1,2))

Thanks Andrew, for your hint! It lead me on the right path :-)

Collectives™ on Stack Overflow

SSIS Convert numeric to datetime

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related