SQL - 秒到日、时、分、秒 [英] SQL - Seconds to Day, Hour, Minute, Second

问题描述

可能的重复:
将秒转换为的有效方法sql server 2005中的分钟和秒

我有一个查询需要以天、小时、分钟、秒格式返回秒.

I have a query which needs to return the second in Day, Hour, Minute, Second format.

下面的代码在少于一天时工作正常，但当秒中的值大于一天时不起作用

The below code works fine when its less than a day, but does not work, when the value in second is greater than a day

PRINT Convert(VarChar, DateAdd(S, 86400, 0), 108)

86400 正好是一天，它返回 00:00:00

86400 is exactly a day and it returns 00:00:00

有人可以修改它并向我展示类似这样的结果

Can someone modify it and show me the result something like this

1:00:00:00.

谢谢

推荐答案

这个怎么样:

declare @days int, 
        @dt1 smalldatetime = '2012-03-25 03:24:16', 
        @dt2 smalldatetime = getdate()

set @days = datediff (s, @dt1, @dt2)


SELECT convert(char(10), @days / (60 * 60 * 24)) + ':'
+ convert(char(10), dateadd(s, @days, convert(datetime2, '0001-01-01')), 108)

Result -- 170:20:40:00

参见SQL Fiddle with Demo

这篇关于SQL - 秒到日、时、分、秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！