Cómo convertir Segundos a HH:MM: SS usando T-SQL
La situación es que tiene un valor en segundos (XXX.XX), y desea convertir a HH:MM:SS usando T-SQL.
Ejemplo:
- 121.25 s se convierte en 00:02: 01.25
11 answers
Desea multiplicar a milisegundos a medida que se descarta la parte fraccionaria.
SELECT DATEADD(ms, 121.25 * 1000, 0)
Si lo desea sin la porción de fecha, puede usar CONVERT, con estilo 114
SELECT CONVERT(varchar, DATEADD(ms, 121.25 * 1000, 0), 114)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-08-11 20:00:47
Si su cantidad de tiempo excede las 24 horas no se manejará correctamente con los métodos DATEADD y CONVERT.
SELECT CONVERT(varchar, DATEADD(ms, 24*60*60 * 1000, 0), 114)
00:00:00:000
La siguiente función manejará tiempos superiores a 24 horas (~máximo 35,791,394 horas).
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
Uso:
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-06-25 14:27:59
Para aquellos que tienen problemas con el uso de DATEADD y CONVERT durante segundos superiores a 24 horas, podemos usar modulus para evitar eso:
SELECT CONVERT(varchar, @seconds / 86400 ) + ':' + -- Days
CONVERT(varchar, DATEADD(ms, ( @seconds % 86400 ) * 1000, 0), 114)
as "Converted to D:HH:MM:SS.MS"
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-10-13 19:14:49
DECLARE @seconds AS int = 896434;
SELECT
CONVERT(varchar, (@seconds / 86400)) --Days
+ ':' +
CONVERT(varchar, DATEADD(ss, @seconds, 0), 108); --Hours, Minutes, Seconds
Salidas:
10:09:00:34
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-02-06 19:04:46
Usando SQL Server 2008
declare @Seconds as int = 3600;
SELECT CONVERT(time(0), DATEADD(SECOND, @Seconds, 0)) as 'hh:mm:ss'
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-09-19 12:39:37
Usando SQL Server 05 puedo hacer que esto funcione usando:
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')
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-08-11 20:00:26
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)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-06-26 05:06:04
Esto es lo que uso (normalmente para los informes de correo electrónico de tabla html)
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-06-29 16:36:32
SELECT substring(convert (varchar(23),Dateadd(s,10000,LEFT(getdate(),11)),121),12,8)
10000 es su valor en sec
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-10-23 15:06:18
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)]
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-10-30 06:58:37
Convierte segundos en horas en la consulta select:
SELECT field_in_seconds,round((field_in_seconds / 3600),2) FROM a_table;
Esto le dará el resultado en formato decimal. No estoy seguro de cómo convertir 1.50 a 1: 30. Por favor, siéntase libre de editar mi código!
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-05-24 19:07:24