TA貢獻(xiàn)1836條經(jīng)驗(yàn) 獲得超3個(gè)贊
您要乘以毫秒，因?yàn)樾?shù)部分將被丟棄。
SELECT DATEADD(ms, 121.25 * 1000, 0)
如果您希望不帶日期部分，可以使用樣式為114的CONVERT
SELECT CONVERT(varchar, DATEADD(ms, 121.25 * 1000, 0), 114)
TA貢獻(xiàn)1789條經(jīng)驗(yàn) 獲得超8個(gè)贊
如果您的時(shí)間量超過24小時(shí)，則DATEADD和CONVERT方法將無法正確處理。
SELECT CONVERT(varchar, DATEADD(ms, 24*60*60 * 1000, 0), 114)
00:00:00:000
以下功能將處理超過24小時(shí)的時(shí)間（最大35,791,394小時(shí)）。
create function [dbo].[ConvertTimeToHHMMSS]
(
@time decimal(28,3),
@unit varchar(20)
)
returns varchar(20)
as
begin
declare @seconds decimal(18,3), @minutes int, @hours int;
if(@unit = 'hour' or @unit = 'hh' )
set @seconds = @time * 60 * 60;
else if(@unit = 'minute' or @unit = 'mi' or @unit = 'n')
set @seconds = @time * 60;
else if(@unit = 'second' or @unit = 'ss' or @unit = 's')
set @seconds = @time;
else set @seconds = 0; -- unknown time units
set @hours = convert(int, @seconds /60 / 60);
set @minutes = convert(int, (@seconds / 60) - (@hours * 60 ));
set @seconds = @seconds % 60;
return
convert(varchar(9), convert(int, @hours)) + ':' +
right('00' + convert(varchar(2), convert(int, @minutes)), 2) + ':' +
right('00' + convert(varchar(6), @seconds), 6)
end
用法：
select dbo.ConvertTimeToHHMMSS(123, 's')
select dbo.ConvertTimeToHHMMSS(96.999, 'mi')
select dbo.ConvertTimeToHHMMSS(35791394.999, 'hh')
0:02:03.000
1:36:59.940
35791394:59:56.400
舉報(bào)