SSIS HH::MM:SS to seconds with the HH part being more than 24 in SSIS-CodePudding

I am trying to have an SSIS derived column that will transform HH:MM:SS from a flat file to just seconds in the database.

The issue I am having and not seeming to find out how to online is when the HH is over 24.

For instance one of the csv file has 178:29:00.

I was using (DT_NUMERIC,10,2)(((DT_I4)SUBSTRING([SPEED OF ANSWER],1,2) * 3600) ((DT_I4)SUBSTRING([SPEED OF ANSWER],4,2) * 60) ((DT_NUMERIC,4,2)RIGHT([SPEED OF ANSWER],2))) when the hours was under 24.

But that does not work if it is over 24.

CodePudding user response：

one way to do it is to split the varchar into different values, and then just sum them.
What you did wrong in your code is that you did not take the number of digits for the hour part in account.
Here is an example on how you could do that, the example is in SQL because I don't know much of ssis.
Hopefully it can help you

declare @s varchar(20) = '178:29:00'

select convert(int, left(@s, charindex(':', @s) - 1)) * 3600  
       convert(int, substring(@s, charindex(':', @s)   1, 2)) * 60  
       convert(int, right(@s, 2))

This is without checking if there are invalid values, you still have to do that.
I also assume that the minute and the second part are always 2 digits long