How to Display beyond 24 hrs in SSRS 2008 in HH:MM:SS format

1
votes

I have a report in SSRS 2008 that pulls data from a table with a column, TotalTime that is an Integer value in seconds.

To display is as time I use this in the expression:

=Format(DateAdd("s", Fields!TotalTime.Value, "00:00:00"), "HH:mm:ss")

(got that from here: Display Seconds Count in HH:MM:SS format in SSRS 2008)

Now, when the value goes beyond 24 hrs it does kind of a "module" value. I mean this: if the value would be 29 hrs, instead of displaying 29:00:00 it will display 05:00:00. If the value if 51 hrs, it will display 03:00:00 (takes out 48 hrs).

How do I solve this?

Thanks!

3
reporting-servicesssrs-2008
Why not just add the amount of days? – eandersson

3 Answers

5
votes

There are 86,400 seconds in a day. So just use days if the seconds are greater than that:

=IIF(Fields!TotalTime.Value < 86400, 
    Format(DateAdd("s", Fields!TotalTime.Value, "00:00:00"), "HH:mm:ss"), 
    Floor(Fields!TotalTime.Value / 86400) & " days, " & Format(DateAdd("s", Fields!TotalTime.Value, "00:00:00"), "HH:mm:ss")
5
votes

For HH:mm:ss format you can use this:

=Floor(Fields!TotalTime.Value / 3600) &":"& Format(DateAdd("s", Fields!TotalTime.Value, "00:00"), "mm:ss")

In this case, for example 90000sec will be displayed as: 25:00:00

For DD:HH:mm:ss format use this:

Floor(Fields!TotalTime.Value / 86400) &":"& Format(DateAdd("s", Fields!TotalTime.Value, "00:00:00"), "HH:mm:ss")

90000sec will be shown as: 1:01:00:00

0
votes

You really need to use the hour based solution, the day based one can potentially get you into trouble with Daylight Savings Time change overs.

=Floor(Fields!TotalTime.Value / 3600) &":"& Format(DateAdd("s", Fields!TotalTime.Value, "00:00"), "mm:ss")