CODEWITHSUNDEEP
datetimestringdb2
WernerCD
WernerCD

Reputation: 2157

SQL Convert Seconds into Day:Hour:Min:Sec

Topic says it all. I'm trying to do some magic, via a function, that turns a second integer into a string "DD:HH:MM:SS".

Snip

input: 278543
output: "3D 5H 22M 23S "

What I'd like to do, more gracefully if possible, is pad the numbers (So that 5M shows as 05M) and right align them so that "3D 5H 22M 23S " is " 3D 5H 22M 23S" instead.

edit: Latest cut that seems to work. Would love to have it prettier, but this definitely works as far as I can tell:

CREATE FUNCTION DHMS(secondsElapsed INT)
RETURNS Char(20)
LANGUAGE SQL
NO EXTERNAL ACTION
DETERMINISTIC

BEGIN
    DECLARE Dy Integer;
    DECLARE Hr Integer;
    DECLARE Mn Integer;
    DECLARE Sc Integer;

    SET Dy = Cast(     secondsElapsed / 86400         as Int);
    SET Hr = Cast(MOD( secondsElapsed, 86400 ) / 3600 as Int);
    SET Mn = Cast(MOD( secondsElapsed, 3600 ) / 60    as Int);
    SET Sc = Cast(MOD( secondsElapsed, 60 )           as Int); 

     RETURN REPEAT(' ',6-LENGTH(RTRIM(CAST(Dy AS CHAR(6))))) || Dy || 'D ' 
         || REPEAT('0',2-LENGTH(RTRIM(CAST(Hr AS CHAR(6))))) || Hr || 'H ' 
         || REPEAT('0',2-LENGTH(RTRIM(CAST(Mn AS CHAR(6))))) || Mn || 'M '
         || REPEAT('0',2-LENGTH(RTRIM(CAST(Sc AS CHAR(6))))) || Sc || 'S';
END

Upvotes: 0

Views: 3829

Answers (2)

Navaid Faooqui
Navaid Faooqui

Reputation: 11

declare @seconds int set @seconds = 900000000 select cast(@seconds/86400 as varchar(50))+':'+Convert(VarChar, DateAdd(S, @seconds, 0), 108)

Upvotes: 1

Fred Sobotka
Fred Sobotka

Reputation: 5332

You were on the right track using LPAD(), since it can pad with zero or any other string. CHAR(15) is not enough to format the output the way you want and still allow five positions for the number of days, which is the length you specified in your code.

CREATE OR REPLACE FUNCTION DHMS(secondsElapsed INT)
RETURNS Char(18)
LANGUAGE SQL
NO EXTERNAL ACTION
DETERMINISTIC

RETURN LPAD( secondsElapsed / 86400 , 5 ) || 'D ' 
  || LPAD( MOD( secondsElapsed, 86400 ) / 3600, 2, '0') || 'H ' 
  || LPAD( MOD( secondsElapsed, 3600 ) / 60, 2, '0' ) || 'M '
  || LPAD( MOD( secondsElapsed, 60 ), 2, '0' ) || 'S'
;

Upvotes: 2

Related Questions