I am working with data containing [duration] in microseconds, some examples:

5982312624, 181240328664, 2981750461, 993806305107, 3015857413, 283850264906, 5987597545, 240770859583

I converted this to number of hours, minutes and seconds as follows:

(CONVERT(VARCHAR(10), Dateadd(second, (((([duration]/10^7)/3600*60)/1000000.0)*60),0),114)) AS [HH:MM:SS]

The problem I am facing is when this exeeds 24 hours it does not register correctly.

Question: I would either like to add a day in here for example: DD:HH:MM:SS -> 01:23:04:33 or just continue the count of hours example: 45:12:52.

Hope anyone can help. Thanks in advance.

CodePudding user response：

Assuming a duration can't be longer than 100 days, which I hope it can't if you're recording microseconds, then given this sample data:

CREATE TABLE dbo.Source(id int identity, duration bigint);

INSERT dbo.Source(duration) VALUES
(5982312624),(181240328664),(2981750461),(993806305107),
(3015857413),(283850264906),(5987597545),(240770859583);

One way is to determine the day of year after adding seconds (not microseconds, which will overflow) to any arbitrary January 1st, and prepend the number of days onto the conversion to time:

SELECT id, duration, 
  [DD:HH:MM:SS] = RIGHT('0'   RTRIM(DATEPART(DAYOFYEAR, dt) - 1), 2)
      ':'   CONVERT(char(8), dt, 108)
  FROM dbo.Source 
  CROSS APPLY 
  (
    VALUES(DATEADD(SECOND, duration/1000000.0, '20000101'))
  ) AS d(dt);

Results:

If it can be more than 100 days (or more than a year), you can make this slight change:

SELECT id, duration, 
  [DDDD:HH:MM:SS] = RIGHT('000'   RTRIM(DATEDIFF(DAY, '20000101', dt)), 4)
      ':'   CONVERT(char(8), dt, 108)
  FROM dbo.Source 
  CROSS APPLY 
  (
    VALUES(DATEADD(SECOND, duration/1000000.0, '20000101'))
  ) AS d(dt);

Results:

And for completeness, if you want all the hours added together instead of separating days, it's actually slightly more complicated:

SELECT id, duration, 
    [HHHH:MM:SS] = RIGHT('000'   CONVERT(varchar(11), 
      24 * DATEDIFF(DAY, '20000101', dt)   DATEPART(HOUR, dt)), 4)
        RIGHT(CONVERT(char(8), dt, 108), 6)
  FROM dbo.Source 
  CROSS APPLY 
  (
    VALUES(DATEADD(SECOND, duration/1000000.0, '20000101'))
  ) AS d(dt);

Results:

If it can be more than 1,000 days, well, perhaps you should consider storing start and end as datetime instead of duration in microseconds.

• Examples at db<>fiddle