SELECT Seconds,
RIGHT('00'+CONVERT(VARCHAR(10),Seconds/3600),2)
+':'
+ RIGHT('00'+CONVERT(VARCHAR(2),(Seconds%3600)/60),2)
+':'
+ RIGHT('00'+CONVERT(VARCHAR(2),Seconds%60),2) AS [HH:MM:SS]
FROM table1
Result:
Seconds |
HH:MM:SS |
3660 |
01:01:00 |
3800 |
01:03:20 |
4200 |
01:10:00 |
600 |
00:10:00 |
60 |
00:01:00 |
86400 |
24:00:00 |
86800 |
24:06:40 |
请参阅此 SQLFiddle http://sqlfiddle.com/#!18/1ee95a/1
Update
如果总小时数小于 100,即 (99:59:59),上述查询可以正常工作。如果您需要更多,可以使用以下查询:
SELECT
Seconds,
CASE Seconds/3600
WHEN 0 THEN RIGHT('00'+CONVERT(VARCHAR(10),Seconds/3600),2)
ELSE CONVERT(VARCHAR(10),Seconds/3600) END
+':'
+ RIGHT('00'+CONVERT(VARCHAR(2),(Seconds%3600)/60),2)
+':'
+ RIGHT('00'+CONVERT(VARCHAR(2),Seconds%60),2) AS [HH:MM:SS]
FROM table1
Result:
Seconds |
HH:MM:SS |
3660 |
1:01:00 |
3800 |
1:03:20 |
4200 |
1:10:00 |
600 |
00:10:00 |
60 |
00:01:00 |
9999930 |
2777:45:30 |
359999 |
99:59:59 |
360000 |
100:00:00 |
86800 |
24:06:40 |
请参阅此 SQLFiddle http://sqlfiddle.com/#!18/01cd4/1