Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JJ297
Aged Yak Warrior
940 Posts |
 Posted - 2011-05-12 : 10:56:47
|
| In my table (T16) I have a column called FLG_CDT char(8) so the date in that column appears as 20100930I want to change the date to appear as 9/30/2010 12:00:00 AMI've added a Data Flow Derived column In the Derived Column I selected replace FLG_CDTWhat do I put in the Expression field and what DataType do I use? |
|
|
latch
Yak Posting Veteran
62 Posts |
Posted - 2011-05-12 : 12:23:04
|
| u can try these:(SUBSTRING(FLG_CDT,5,2) + "/" + SUBSTRING(FLG_CDT,7,2) + "/" + SUBSTRING(FLG_CDT,1,4) + " 12:00:00 AM")or(DT_DBTIMESTAMP)(SUBSTRING(FLG_CDT,5,2) + "/" + SUBSTRING(FLG_CDT,7,2) + "/" + SUBSTRING(FLG_CDT,1,4) + " 12:00:00 AM") |
 |
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 2011-05-13 : 11:12:03
|
| I tried both expressions but I got this error message:[Derived Column [1296]] Error: The "component "Derived Column" (1296)" failed because truncation occurred, and the truncation row disposition on "input column "FLG_CDT" (1310)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column" (1296) failed with error code 0xC020902A. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failureThe date type wouldn't allow me to change it to date [DT_DATE]time and the length is at 8. |
|
|
|
latch
Yak Posting Veteran
62 Posts |
Posted - 2011-05-16 : 15:58:32
|
| Are you saving the value to same column?its of 8 length then it won't take it thats why truncation error.Can you increase the length of it and try. |
|
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 2011-05-16 : 18:11:42
|
| I would like to save it to the same column. It won't allow me to increase the length. Am I using the right transformation? |
|
|
|
|
|
|
|
|
Replace derived column in SSIS