ssisdatetime-conversionderived-column

no leading zero in hours h:mm:ss time format to convert to seconds in a derived column in SSIS

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)

enter image description here

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

enter image description here

Whereas the result should be like:

Time Time Result
1:00:00 3600
:30:00 1800
:53:14 3194
Solution

  • I think your expression is good except you need another case for time values with length of 7, like 1:00:00. You have 6, for values like :00:00, and 8 for values like 10:00:00, but you need one for 7 length values.

    Try this:

    (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) == 7 ? ((DT_I4)(DT_STR,8,1254)LEFT(TIME,1) * 3600) + ((DT_I4)(DT_STR,8,1254)SUBSTRING(TIME,3,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)