CODEWITHSUNDEEP
reporting-servicesssrs-2008
Amarundo
Amarundo

Reputation: 2397

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

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!

Upvotes: 1

Views: 10252

Answers (3)

Lucas Tieman
Lucas Tieman

Reputation: 81

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")

Upvotes: 1

Konrad Z.
Konrad Z.

Reputation: 1652

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

Upvotes: 5

Chris Latta
Chris Latta

Reputation: 20560

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")

Upvotes: 5

Related Questions