T-SQL Format seconds as HH:MM:SS time

Question

Is there any tricky way to format seconds like hours:minutes:seconds. For example,

3660

seconds will be displayed as

01h 01m 00s

or

01:01:00

I am aware of the standard way of doing this:

Divide all seconds on 3600 to get the hours
Divide the rest seconds on 60 to get the minutes
The rest are the seconds

I met the following issues:

I am not able to create separate function that do this.
My code is in view using several CTEs. So, variables can be declare using the CTEs only.
I am not able to use the standard solution because I will have results bigger then one day - How to convert Seconds to HH:MM:SS using T-SQL

So if you have 86399 seconds, you want 23:59:59, but if you have 86400 seconds, you want 24:00:00, etc.? — LittleBobbyTables - Au Revoir, Nov 01 '12 at 12:43
If I have 3 days in seconds it will shows 72:00:00. I get your point, I should have said that there is no max value for hours. — gotqn, Nov 01 '12 at 12:45

Himanshu · Accepted Answer · 2023-02-09T14:17:37.450

SELECT Seconds, 
    RIGHT('00'+CONVERT(VARCHAR(10),Seconds/3600),2)  
    +':' 
    + RIGHT('00'+CONVERT(VARCHAR(2),(Seconds%3600)/60),2) 
    +':' 
    + RIGHT('00'+CONVERT(VARCHAR(2),Seconds%60),2) AS [HH:MM:SS] 
FROM table1

Result:

Seconds	HH:MM:SS
3660	01:01:00
3800	01:03:20
4200	01:10:00
600	00:10:00
60	00:01:00
86400	24:00:00
86800	24:06:40

See this SQLFiddle

Update

The above query works fine if the total number of hours are less than 100 i.e. (99:59:59). If you need more than that you can use the following query:

SELECT 
    Seconds,
    CASE Seconds/3600 
    WHEN 0 THEN RIGHT('00'+CONVERT(VARCHAR(10),Seconds/3600),2) 
    ELSE CONVERT(VARCHAR(10),Seconds/3600) END
    +':' 
    + RIGHT('00'+CONVERT(VARCHAR(2),(Seconds%3600)/60),2) 
    +':' 
    + RIGHT('00'+CONVERT(VARCHAR(2),Seconds%60),2) AS [HH:MM:SS]
FROM table1

Result:

Seconds	HH:MM:SS
3660	1:01:00
3800	1:03:20
4200	1:10:00
600	00:10:00
60	00:01:00
9999930	2777:45:30
359999	99:59:59
360000	100:00:00
86800	24:06:40

See this SQLFiddle

score 8 · Answer 2 · edited Jun 20 '20 at 09:12

8

Try this:

SELECT CONVERT(TIME(0), DATEADD(SS,***seconds***,0),108) as 'FieldName'

will return HH:MM:SS

Example:

46800 seconds = 1:00 PM

FieldName = 13:00:00

Hope this helps.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 09 '15 at 20:33

Greg

83
2
4

I think this should be the preferred solution if to convert a column on an entire table of records. – Mr. Blonde Dec 20 '16 at 10:07
2

Note that the TIME datatype does not support values greater than 86,399, so this does not work for the original intent where times may reflect more than 24 hours. – Tim Jan 14 '21 at 19:41

score 3 · Answer 3 · answered Nov 01 '12 at 14:31

3

SELECT cast(seconds/3600 as varchar(6)) +
right(cast(dateadd(second, seconds,0) as time(0)), 6)
FROM <yourtable>

answered Nov 01 '12 at 14:31

t-clausen.dk

43,517
12
59
92

T-SQL Format seconds as HH:MM:SS time

3 Answers3

Update

Linked