sqlsql-servert-sqldatetimedate-conversion

Error converting an int to a time (HH:mm:ss)

I have the avg of multiple datediffs converted in seconds and I wanted to display them as a timestamp, the problem is that some numbers greater than others give me a timestamp shorter, how can I get the correct times? Example: these are 4 different values that I need to convert

select CONVERT(CHAR(8), DATEADD(ss, 147588, 0), 108)
union all
select CONVERT(CHAR(8), DATEADD(ss, 63297, 0), 108)
union all
select CONVERT(CHAR(8), DATEADD(ss, 67232, 0), 108)
union all
select CONVERT(CHAR(8), DATEADD(ss, 97230, 0), 108)

And this is the output

16:59:48
17:34:57
18:40:32
03:00:30

By correct time I mean that I need to get the number of hours (like 40:23:56)

Solution

  • Something like:

    select CONCAT(147588/3600,':',(147588/60)%60,':',147588%60)

    seems to work.

    Edit: Well, in fact:

    select CONCAT(147588/3600,':',FORMAT((147588/60)%60,'D2'),':',FORMAT(147588%60,'D2'))

    should be better, for the case where the minutes count, or the seconds count, is between 00 and 09, and you want that leading zero.