如何将负秒转换为HH:MM:SS ?

I am trying to convert a field with positive and negative seconds to HH:MM:SS but failing in the process.

我正在尝试将一个正负秒的字段转换为HH:MM:SS，但是在这个过程中失败了。

CONVERT(VARCHAR(6), RPAYCODE.TIMEINSECONDS/3600) +
':' + 
RIGHT('0' + CONVERT(VARCHAR(2), (RPAYCODE.TIMEINSECONDS % 3600) / 60), 2)   
AS "Pay Code Hrs"

When the seconds are positive it works fine = 00:30:00

当秒是正的时，它工作得很好= 00:30 . 00

When the seconds are negative I get this = 0:0*

当秒为负时，得到= 0:0*

2 个解决方案

#1

This should handle the negative values:

这应该处理负值:

CASE
    WHEN @time_in_seconds < 0 THEN '-'
    ELSE ''
END +
CONVERT(VARCHAR(6), ABS(RPAYCODE.TIMEINSECONDS)/3600) + ':' +
RIGHT('0' + CONVERT(VARCHAR(2), (ABS(RPAYCODE.TIMEINSECONDS) % 3600)/60), 2) AS [Pay Code Hrs]

#2

Lean and simple conversion

Skip all those unnecessary calculations and string operations:

跳过所有不必要的计算和字符串操作:

CASE WHEN RPAYCODE.TIMEINSECONDS < 0 THEN '-' ELSE '' END  -- sign
+ CONVERT(varchar, DATEADD(s, ABS(RPAYCODE.TIMEINSECONDS), 0), 108) -- hh:mm:ss

Number format 108 is hh:mm:ss.

数字格式108为hh:mm:ss。