Derived Column replace column

[nc297]

In my table (T16) I have a column called FLG_CDT char(8) so the date in that column appears as 20100930

I want to change the date to appear as 9/30/2010 12:00:00 AM

I've added a Data Flow Derived column

In the Derived Column I selected replace FLG_CDT

What do I put in the Expression field and, it won't allow me to change the datatype. What am I doing wrong?

 

[ousoonerjoe]

Here's one way. There may be a better way, but this works:

DECLARE @Dat VARCHAR(8);
SET @Dat = '07041776'
SELECT CAST(LEFT(@Dat, 2) + '/' + SUBSTRING(@Dat, 3, 2) + '/' + RIGHT(@Dat, 4) AS DATETIME);

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[ousoonerjoe]

oops... noticed as i hit submit, i had the string date out of order:

DECLARE @Dat VARCHAR(8);
SET @Dat = '17760704'
SELECT CAST(RIGHT(@Dat, 2) + '/' + SUBSTRING(@Dat, 5, 2) + '/' + LEFT(@Dat, 4) AS DATETIME);

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------