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)

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)

• sql
• sql-server
• t-sql
• datetime
• date-conversion

• 1 display them as a timestamp you mean interval or period? You can't do that in T_SQL. SQL Server doesn't have an interval type and a timestamp would be 1900-01-02 16:59:48, not 1d 16:59:48. Never mind DST changes and leap seconds. A client language on the other hand, eg C#, would have types like TimeSpan that can represent an interval, with easy parsing and appropriate formatting – Panagiotis Kanavos Commented Mar 31 at 10:27
• I wanted to display them where? In what language? – Panagiotis Kanavos Commented Mar 31 at 10:28
• Depends on what you mean by correct times? What would you expect to be the "correct" output for 147588? I suppose 40:59:48? But that's not possible with your approach, as each day (and that's what you are using) only has a maximum of 24 hours and there is no type for intervals in TSQL – derpirscher Commented Mar 31 at 10:29
• Why not add what you want to display as well as what you got already? It's literally 20 more characters – siggemannen Commented Mar 31 at 10:56
• @derpirscher Yes, I need that output, sorry for the poorly structured question but I was in a hurry – Lorenzo Santarelli Commented Mar 31 at 11:01

3 Answers 3

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.

This is completely expected. You are adding seconds to the datetime 0 (1900-01-01 00:00:00) and then converting that that value to string (not a time) in the format hh:mm:ss. Some of your values are greater than there are seconds in a day (86400), so you get the time for the relevant date.

Example CONVERT(CHAR(8), DATEADD(ss, 147588, 0), 108):

• Add 147588 seconds (1 day, 61188 seconds) to the datetime 1900-01-01 00:00:00 : 1900-01-02 16:59:48.000
• Return the time portion of the datetime value as a varchar, in the format hh:mm:ss: '16:59:48'

Thus the results are correct, as you are comparing the time of day to another time of day; the date is irrelevant. 18:40:32 is later than 16:59:48, when you don't consider the date.

You want the duration as hh:mm:ss (with up to 99 hours) it seems. Use integer math for this:

select 
  secs,
  format(hours, '00') + ':' + format(minutes, '00') + ':' + format(seconds, '00')
from
(
  select
    secs,
    secs / 60 / 60 as hours,
    (secs - (secs / 60 / 60 * 60 * 60)) / 60 as minutes,
    secs - (secs / 60 * 60) as seconds
  from mytable
) calculated;

(This only works if your secs column is an integer of type INT. If it is an integer of type DECIMAL(..., 0) for instance, then you will have to convert it first.)

Demo: https://dbfiddle.uk/hvM24mqM