How to convert Seconds to HH:MM:SS using T-SQL

Question

The situation is you have a value in Seconds (XXX.XX), and you want to convert to HH:MM:SS using T-SQL.

Example:

121.25 s becomes 00:02:01.25

https://www.sqlservercentral.com/Forums/FindPost1267364.aspx — Kennah, May 05 '17 at 18:32

score 165 · Accepted Answer · answered Aug 11 '09 at 20:00

165

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)

answered Aug 11 '09 at 20:00

great_llama

11,481
4
34
29

3

SELECT CONVERT(varchar, DATEADD(ms, 121.25 * 1000, 0), 114) is it. – Brett Veenstra Aug 11 '09 at 20:10
29

This won't work if you have more than 24 hours worth of seconds – JamWheel Mar 15 '11 at 15:03
27

Very neat. Use 108 if you don't want milliseconds, ie hh:mi:ss – stuartd Jul 04 '13 at 10:01
8

If you have SQL 2008+ and just want the time portion; instead of converting to varchar: SELECT CONVERT(time, DATEADD(ms, 121.25 * 1000, 0)) – Eric Humphrey Aug 01 '13 at 19:42
You can also do FORMAT(..., 'mm:ss') – Shiroy Mar 15 '22 at 23:38

score 59 · Answer 2 · answered Jun 25 '12 at 14:27

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

Why the time longer then 24 hours will not be formatted correctly using DateAdd? — gotqn, Nov 01 '12 at 12:13
@gotqn: DateAdd() isn't the problem. Convert() is the problem. Convert() effectively wraps around to zero every 24 hours. If you use Convert() to format a 27-hour interval, it will return "03:00:00". — Mike Sherrill 'Cat Recall', Dec 14 '12 at 02:29
How to handle this formatting if the use case says results can be negative hh:mm:ss (eg. -340:23:34, 03:-4:05,13:54:-8 etc) — OmGanesh, Sep 14 '17 at 00:52
This is the way to go. None of the other solutions here will work properly for HHMMSS formatting beyond 24 hours. This will also help keep your queries neat. — Krummelz, May 14 '19 at 00:26
Just be aware of the performance implications this function may have on larger datasets, and test accordingly. Especially with it being non-inlined causing any dependent queries (at any level) to not be applicable for parallelization. — J.D., Aug 10 '23 at 15:41

score 31 · Answer 3 · answered Oct 13 '14 at 19:14

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"

score 29 · Answer 4 · answered Feb 06 '14 at 19:04

29

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

answered Feb 06 '14 at 19:04

cmd.prompt

954
7
14

4

This should be the accepted answer -- it deals with `second`s, as the O.P. asked, avoids using an inefficient `scalar function`, and deals with multi-day spans appropriately. The only minor nit I would add is that, in SQL Agent land, your elapsed-time/time-intervals for the "days" part typically use "days.hh:mm:ss", i.e. a `dot` instead of `colon`. So for example `2.01:03:04` for 2 days, 1 hour, 3 minutes, 4 seconds. Cheers! – NateJ Jul 06 '17 at 22:31

score 16 · Answer 5 · answered Sep 19 '16 at 12:39

16

Using SQL Server 2008

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

answered Sep 19 '16 at 12:39

Pranesh Janarthanan

1,134
17
26

3

let me know the reason for downvoting. – Pranesh Janarthanan Jan 21 '17 at 11:36
Can't assign the result to a variable For example: – Dan H. Aug 25 '22 at 20:25

score 5 · Answer 6 · answered Aug 11 '09 at 20:00

5

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')

answered Aug 11 '09 at 20:00

Richard Reddy

2,120
3
25
39

1

this did not work for me. if you pass 896434 seconds - you want 10:09:00:34:000 and not 249:00:34 – Marcello Miorelli Sep 21 '16 at 13:08

G.P. · Answer 7 · 2022-04-05T12:12:00.500

3

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);

edited Apr 05 '22 at 12:12

answered Apr 05 '22 at 11:59

G.P.

31
3

The FORMAT function is about 22 times slower than just about any other method you could come up with. – Jeff Moden Feb 14 '23 at 19:44
I'll take your word for it. Have never had queries where this impacted performance. – G.P. Feb 16 '23 at 08:41

score 2 · Answer 8 · answered Jun 26 '15 at 05:06

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)

score 1 · Answer 9 · edited Oct 23 '13 at 15:06

1

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

10000 is your value in sec

edited Oct 23 '13 at 15:06

kmas

6,401
13
40
62

answered Oct 23 '13 at 14:35

yacob

11
1

score 1 · Answer 10 · answered Jun 29 '15 at 16:36

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

score 0 · Answer 11 · answered Oct 30 '17 at 06:58

0

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)]

answered Oct 30 '17 at 06:58

Rajiv Singh

958
1
9
14

score 0 · Answer 12 · edited Apr 23 '19 at 20:35

0

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

edited Apr 23 '19 at 20:35

slfan

8,950
115
65
78

answered Apr 23 '19 at 20:09

anas bouhajbi

1

score 0 · Answer 13 · edited Apr 21 '21 at 18:12

0

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)]

edited Apr 21 '21 at 18:12

M-Chen-3

2,036
5
13
34

answered Apr 21 '21 at 15:52

David Allan James

13
2

score 0 · Answer 14 · answered Feb 14 '23 at 02:08

Maybe you can use this:

To get a value with time datatype:

select convert(time(0),cast(0 as datetime)+3661*1e0/24/60/60)

To get a value with char datatype:

select convert(char,cast(0 as datetime)+3661*1e0/24/60/60,108)

time(0) returns a time with minimal precision (hh:mm:s)
cast(0 as datetime) returns 1900-01-01 ...
3661 is your number in seconds= 1 hour 1 minute 1 second
1e0 prevent truncations (it's the number 1 expanded with decimal)
datetime+1= tomorrow, and datetime+1e0/24/60/60= 1 second
or replace that, with .../24/3600 (one less calculation)

Output:

01:01:01

score -1 · Answer 15 · edited Jul 22 '22 at 00:57

-1

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

edited Jul 22 '22 at 00:57

Jeremy Caney

7,102
69
48
77

answered Jul 19 '22 at 11:04

Moshe Klien

19
3

4

Please don't post code-only answers. Future readers will be grateful to see explained *why* this answers the question instead of having to infer it from the code. Also, since this is an old question, please explain how it complements *all* other answers. (BTW, the answer isn't correct) – Gert Arnold Jul 19 '22 at 11:17

How to convert Seconds to HH:MM:SS using T-SQL

15 Answers15

Linked

Related