Convert seconds to HH:MM:SS in SQL Server

Question

I need help in converting seconds to HH:MM: SS in SQL Server.

This is the code I have:

SELECT
    CONVERT(varchar(8), DATEADD(SECOND, DATEDIFF(SECOND, '2020-11-28 16:03:53.200', '2020-11-28 16:25:53.200'), 0), 114) AS Runtime

OUTPUT: 00:22:00

The above code works when the date difference is within one day, but if the date difference is more than one day then this code is not working

SELECT
    CONVERT(varchar(8), DATEADD(SECOND, DATEDIFF(SECOND, '2020-11-28 16:03:53.200', '2020-11-29 16:25:53.200'), 0), 114) AS Runtime

OUTPUT : 00:22:00

Thanks,

SQL Server doesn't have have a timespan datatype, just a `time` data type that represents a *time* of day; therefore the value cannot be 24:00:00 or greater, as no such time exists. — Larnu, Jun 01 '22 at 07:49

score -1 · Answer 1 · edited Jun 01 '22 at 07:11

-1

SELECT SEC_TO_TIME( seconds ); SELECT SEC_TO_TIME( seconds ); In this query, the “seconds” parameter is the number of seconds to convert. The result will be displayed in “HH:MM:SS” format.

https://stackhowto.com/mysql-how-to-convert-seconds-to-hhmmss-format/

edited Jun 01 '22 at 07:11

Dale K

21,987
13
41
69

answered Jun 01 '22 at 07:07

IslamYearul

33
1
7

SEC_TO_TIME is not a function in the SQL server – Vinoth .R Jun 01 '22 at 07:13
@DaleK Sorry about that : \ – Vinoth .R Jun 01 '22 at 07:19
Same as this? https://stackoverflow.com/questions/1262497/how-to-convert-seconds-to-hhmmss-using-t-sql – JonTout Jun 01 '22 at 07:20
@JonTout I tried that, but it doesn't seem to work if the date difference is more than one day – Vinoth .R Jun 01 '22 at 07:23
or this? https://stackoverflow.com/questions/758891/calculating-timespan-with-t-sql Why aren't you displaying the difference between two dates in HH:MM:SS other than in SS and then converting? – JonTout Jun 01 '22 at 07:35
That link doesn't have an answer for the above issue, Actually I was trying to display the difference between two dates in HH:MM:SS – Vinoth .R Jun 01 '22 at 09:13

Convert seconds to HH:MM:SS in SQL Server

1 Answers1