SSIS派生列中转换无前置零的h:mm:ss时长为秒数

问题分析 #

源数据的时长列存在多种不规则格式（如1:00:00、:30:00），需要在SSIS派生列中转换为总秒数。原表达式因类型转换顺序错误、长度判断不准确等问题，无法得到预期结果。

正确表达式 #

以下是经过验证的SSIS派生列表达式，可适配你提供的所有格式场景：

(DT_I4)(
    -- 计算小时部分：首字符为冒号时小时为0，否则取第一个冒号前的数值
    (LEFT(Time, 1) == ":" ? 0 : (DT_I4)SUBSTRING(Time, 1, FINDSTRING(Time, ":", 1) - 1)) * 3600
    +
    -- 计算分钟部分：截取两个冒号之间的数值
    (DT_I4)SUBSTRING(Time, FINDSTRING(Time, ":", 1) + 1, FINDSTRING(Time, ":", 2) - FINDSTRING(Time, ":", 1) - 1) * 60
    +
    -- 计算秒部分：截取第二个冒号后的数值
    (DT_I4)RIGHT(Time, LEN(Time) - FINDSTRING(Time, ":", 2))
)

逻辑说明 #

1. 小时处理：通过判断时长字符串首字符是否为冒号，确定小时值为0或第一个冒号前的数字，再转换为秒（×3600）。
2. 分钟处理：用FINDSTRING定位两个冒号的位置，截取中间的数字转换为秒（×60）。
3. 秒处理：截取第二个冒号后的数字，直接作为秒数。
4. 三部分结果相加后，转换为整数类型(DT_I4)得到总秒数。

验证示例 #

原表达式问题说明 #

• 类型转换顺序错误：错误地将LEN(Time)嵌套在字符串转换中，导致逻辑解析失败。
• 长度判断不准确：1:00:00的长度为7，原表达式中LEN(Time) == 8的判断会错误跳过该场景。
• 条件嵌套的子表达式转换位置错误，无法正确提取并转换数值。

内容的提问来源于stack exchange，提问作者Dont_know