Topic: Conversion of seconds in .:: or :: more than 24 hours

Prompt, please, as it is possible to convert second in
Format of Dni.chch:mm:ss or :: (where more than 24 hours, for example 56:00:00)
Using the given conversion, time more than 24 hours it starts to be displayed incorrectly, for example 194500 seconds are displayed in a type of 6:01:40 AM
,CONVERT (time (0), DATEADD (s, DurationS, 0), 114) as [Time in driving]

Re: Conversion of seconds in .:: or :: more than 24 hours

``select d = 194500 / (24*60*60), h = 194500 % (24*60*60) / (60*60), m = 194500 % (60*60) / (60), s = 194500 % (60)``

Line that you will make hastily?

Re: Conversion of seconds in .:: or :: more than 24 hours

,REPLACE (STR (DurationS/3600) + ': ' + STR (DurationS%3600/60) + ': ' + STR (DurationS%60), ' ', ")
And here doing in such a way time it is transformed normally, but it is ugly, without 0.
Somehow it is possible to transform, that 0 it was added?
54:1:40-&gt; 54:01:40
22:40:1-&gt; 22:40:01
22:8:5-&gt; 28:08:05

Re: Conversion of seconds in .:: or :: more than 24 hours

``````DECLARE
@d1 DATETIME = DATEADD (HOUR,-56, GETDATE ());
@d2 DATETIME = DATEADD (SECOND, 3, GETDATE ())
DECLARE
@d3 DATETIME = @d2 - @d1
SELECT
CONVERT (VARCHAR, DATEDIFF (DAY, ' 19000101 ', @d3)) + '. ' + SUBSTRING (CONVERT (VARCHAR, @d3, 120), 12, 8)``````

I so made, if only with leading zero not to be fooled.

Re: Conversion of seconds in .:: or :: more than 24 hours

RC88 wrote:

, REPLACE (STR (DurationS/3600) + ': ' + STR (DurationS%3600/60) + ': ' + STR (DurationS%60), ' ', ")
And here doing in such a way time it is transformed normally, but it is ugly, without 0.
Somehow it is possible to transform, that 0 it was added?
54:1:40-&gt; 54:01:40
22:40:1-&gt; 22:40:01
22:8:5-&gt; 28:08:05

``select right (' 0 ' + cast (1 as nvarchar (16)), 2)``

Re: Conversion of seconds in .:: or :: more than 24 hours

RC88 wrote:

, REPLACE (STR (DurationS/3600) + ': ' + STR (DurationS%3600/60) + ': ' + STR (DurationS%60), ' ', ")
And here doing in such a way time it is transformed normally, but it is ugly, without 0.
Somehow it is possible to transform, that 0 it was added?
54:1:40-&gt; 54:01:40
22:40:1-&gt; 22:40:01
22:8:5-&gt; 28:08:05

Replaced gaps with blank line, and could on ' 0 '. Here also there would be in the lead zero...

``,REPLACE (STR (DurationS/3600), ' ', ") + ': ' + REPLACE (STR (DurationS%3600/60,2) + ': ' + STR (DurationS%60,2), ' ', ' 0 ')``

Only zero at hours so do not turn out, for in hours can be more than two digits, and in this case in the lead 0 as though is not necessary

Re: Conversion of seconds in .:: or :: more than 24 hours

iap;
Huge to you Thanks!