Problems while converting string to date.

Hi Guys,

Source Column Data type: Varchar

Destination Data Type: smalldatetime

We have 2 columns effective_Date and Expiration_Date:

the format of data in these columns is not consistent.

Sample values:

1/23/2006

Tuesday, June 21, 2005

1/0/1900

8-Mar-06,

NULL

Now, i want to convert to a standard date format like "2006-03-08 00:00:00".

please let me know the steps to do..

Thanks in Advance.

Sri

Use the Derived Column component. This is simply a string manipulation problem.

-Jamie

|||

Thanks Jamie,

Can you please eloborate the process with the same example.

|||

Hi Jamie,

As i told earlier the date column has different formats of date,

please help me in finding the solution.

how many derived column transformations i have to use..is it for every format of date i have to use one ?

for eg if date format is Wednesday, June 01, 2005 then can you please explain me how to write the code in derived column transformation?

I appreciate your help.

|||

That looks challenging....may be you can use a script component with Regular expressions to parse the date and split the rows according to their format; then you can have expressions to cast them accordingly...

This is just a thought...sorry I have not examples

Rafael Salas

|||

Thanks Rafael...atleast i have something to start..

Where can we implement case statement ..

can we do that in derived column transformation?

let me try this..

Thanks

Sri

|||

You could use a series of nested SSIS conditional operator inside of the derived columns; but the expression can get very large and hard to read/debug.

You can see an example of the conditional operator here:

http://msdn2.microsoft.com/en-us/library/ms141680.aspx

Rafael Salas