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 |
|
elliotencore
Starting Member
2 Posts |
 Posted - 2015-02-27 : 04:29:07
|
| I have some data from an external source, and the times are formatted as "153625" meaning "15:36:25". In SQL I can use STUFF to change this to time format:cast(STUFF(STUFF(RIGHT('000000' + CAST(act_time AS VARCHAR),6),3,0,':'),6,0,':') AS Time)however I can't apply the same to a Derived Column in SSIS - does anybody know how I can achieve the same results using a derived column? |
|
|
elliotencore
Starting Member
2 Posts |
Posted - 2015-02-27 : 11:19:16
|
| Nevermind I found the solution, derived column:(DT_DBTIME)(SUBSTRING(RIGHT(REPLICATE("0",6) + [Copy of act_time],6),1,2) + ":" + SUBSTRING(RIGHT(REPLICATE("0",6) + [Copy of act_time],6),3,2) + ":" + SUBSTRING(RIGHT(REPLICATE("0",6) + [Copy of act_time],6),5,2)) |
 |
|
|
|
|
|
|
|
Converting "153625" to "15:36:25" time field