Display Seconds Count in HH:MM:SS format in SSRS 2008?

Question

I have a table with a column, TotalTime that is an Integer value in seconds. In Visual Studio / SSRS 2008 I want to display it in HH:MM:SS format. Thanks!

Answer 1

A solution to your situation would be to add the seconds to a DATE datatype without any time specified. This will default the date to 00:00:00. Then you can add the SECONDs to the date, and CAST() the value as TIME(0). Where the TIME datatype's offset represents how many millisecond spaces you want. In this case, you are do not want any milliseconds.

Another would be to use the CONVERT() function's formatting overload. You still need to create a date using the same DATEADD(SECOND,) approach.. but format 8,24, and 108 will also return the same results.

DECLARE @Times TABLE (ValueInSeconds INT)

INSERT INTO @Times (ValueInSeconds)

VALUES

(30)

, (120)

, (3484)

, (882716)

, (27716231)

SELECT

CAST(DATEADD(SECOND,ValueInSeconds,'1/1/2000') as TIME(0)) Cast_as_Time0

,CONVERT(VARCHAR(100),DATEADD(SECOND,ValueInSeconds,'1/1/2000'),8 ) Convert_Format_8

,CONVERT(VARCHAR(100),DATEADD(SECOND,ValueInSeconds,'1/1/2000'),24 ) Convert_Format_24

,CONVERT(VARCHAR(100),DATEADD(SECOND,ValueInSeconds,'1/1/2000'),108 ) Convert_Format_108

FROM @Times

/*

OUTPUT

Cast_as_Time0 Convert_Format_8 Convert_Format_24 Convert_Format_108

00:00:30 00:00:30 00:00:30 00:00:30

00:02:00 00:02:00 00:02:00 00:02:00

00:58:04 00:58:04 00:58:04 00:58:04

05:11:56 05:11:56 05:11:56 05:11:56

18:57:11 18:57:11 18:57:11 18:57:11

*/