a source flat file has duration columns in :00:00 format as well as 0:00:00 format. Trying to convert to seconds when there’s no leading zeros in the hours. Sample of the Source file when viewed in notepad++
enter image description here](https://i.sstatic.net/62SvtuBM.png)
I’ve tried the following to convert into seconds using a derived column:
(DT_I4)(DT_STR,8,1254)LEN(Time) == 8 ? ((DT_I4)(DT_STR,8,1254)LEFT(Time,2) * 3600) + ((DT_I4)(DT_STR,8,1254)SUBSTRING(Time,4,2) * 60) + ((DT_I4)(DT_STR,8,1254)RIGHT(Time,2)) : ((DT_I4)(DT_STR,8,1254)LEN(Time) == 6 ? ((DT_I4)(DT_STR,8,1254)SUBSTRING(Time,2,2) * 60) + ((DT_I4)(DT_STR,8,1254)RIGHT(Time,2)) : ((DT_I4)(DT_STR,8,1254)RIGHT(Time,2)))
but not getting the desired result, getting this instead
Whereas the result should be like:
| Time | Time Result |
|---|---|
| 1:00:00 | 3600 |
| :30:00 | 1800 |
| :53:14 | 3194 |