如何使用 T-SQL 将秒转换为 HH: MM: SS

小开

Using SQL Server 05 I can get this to work by using:

declare @OrigValue int;
set @OrigValue = 121.25;
select replace(str(@OrigValue/3600,len(ltrim(@OrigValue/3600))+abs(sign(@OrigValue/359999)-1)) + ':' + str((@OrigValue/60)%60,2) + ':' + str(@OrigValue%60,2),' ','0')

小开

最佳答案

You want to multiply out to milliseconds as the fractional part is discarded.

SELECT DATEADD(ms, 121.25 * 1000, 0)

If you want it without the date portion you can use CONVERT, with style 114

SELECT CONVERT(varchar, DATEADD(ms, 121.25 * 1000, 0), 114)

小开

If your time amount exceeds 24 hours it won't be handled correctly with the DATEADD and CONVERT methods.

SELECT CONVERT(varchar, DATEADD(ms, 24*60*60 * 1000, 0), 114)
00:00:00:000

The following function will handle times exceeding 24 hours (~max 35,791,394 hours).

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

Usage:

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

小开

SELECT substring(convert (varchar(23),Dateadd(s,10000,LEFT(getdate(),11)),121),12,8)

10000 is your value in sec

小开

DECLARE @seconds AS int = 896434;
SELECT
CONVERT(varchar, (@seconds / 86400))                --Days
+ ':' +
CONVERT(varchar, DATEADD(ss, @seconds, 0), 108);    --Hours, Minutes, Seconds

Outputs:

10:09:00:34

小开

For those having issues with using DATEADD and CONVERT for seconds exceeding 24 hours, we can use modulus to get around that:

SELECT CONVERT(varchar, @seconds / 86400 ) + ':' + -- Days
CONVERT(varchar, DATEADD(ms, ( @seconds % 86400 ) * 1000, 0), 114)
as "Converted to D:HH:MM:SS.MS"

小开

DECLARE @TimeinSecond INT
SET @TimeinSecond = 340 -- Change the seconds
SELECT RIGHT('0' + CAST(@TimeinSecond / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((@TimeinSecond / 60) % 60 AS VARCHAR),2)  + ':' +
RIGHT('0' + CAST(@TimeinSecond % 60 AS VARCHAR),2)

小开

This is what I use (typically for html table email reports)

declare @time int, @hms varchar(20)
set @time = 12345
set @hms = cast(cast((@Time)/3600 as int) as varchar(3))
+':'+ right('0'+ cast(cast(((@Time)%3600)/60 as int) as varchar(2)),2)
+':'+ right('0'+ cast(((@Time)%3600)%60 as varchar(2)),2) +' (hh:mm:ss)'
select @hms

小开

Using SQL Server 2008

declare @Seconds as int = 3600;
SELECT CONVERT(time(0), DATEADD(SECOND, @Seconds, 0)) as 'hh:mm:ss'

小开

DECLARE @Seconds INT = 86200;
SELECT
CONVERT(VARCHAR(15),
CAST(CONVERT(VARCHAR(12), @Seconds / 60 / 60 % 24)
+':'+ CONVERT(VARCHAR(2), @Seconds / 60 % 60)
+':'+ CONVERT(VARCHAR(2), @Seconds % 60) AS TIME), 100) AS [HH:MM:SS (AM/PM)]

小开

You can try this

set @duration= 112000
SELECT
"Time" = cast (@duration/3600 as varchar(3)) +'H'
+ Case
when ((@duration%3600 )/60)<10 then
'0'+ cast ((@duration%3600 )/60)as varchar(3))
else
cast ((@duration/60) as varchar(3))
End

小开

I use this:

cast(datediff(hh, '1900-01-01', dateadd(s, @Seconds), 0)) as varchar(10))
+ right(convert(char(8), dateadd(s, @Seconds), 0), 108),6) AS [Duration(H:MM:SS)]

小开

Just in case this might be still interesting to anyone. The 'Format' Function can also be used, with SQL Server 2012+

Declare @Seconds INT = 1000000;
SELECT FORMAT(CAST(@Seconds/86400.000 AS datetime), 'HH:mm:ss');

OR

Declare @Seconds INT = 1000000;
SELECT CAST(FORMAT(CAST(@Seconds/86400.000 AS datetime), 'HH:mm:ss') AS TIME);

小开

CAST((Duration)/60 AS NVARCHAR(9)) + ':' + RIGHT('00' + CAST(Duration % 60 AS NVARCHAR(9)), 2)