Microsoft Reporting Services -- How to convert milliseconds to dd:mm:ss format

user_13443514 0
Viewed 41

I have a milliseconds value in my database (105823 ms) that I want to convert to a dd:hh:mm:ss value. This is the closest I can get is using the following code.

=Format(DateAdd("s",Fields!TimeSinceLastReset.Value , "00:00:00"), "dd:hh:mm:ss")

This works as long as it is less than 24 hours if over the dd is off, it is using 12 hour instead of 24 hour.
My result is 02:05:23:43. I do not really need the seconds just the hh:mm would be fine. the hours can be over 24. Thanks

reporting-services
1 Answers

I would just do the conversion manually rather than try to make DATEADD and FORMAT work the way you need it to. The hours from FORMAT will never show 0, nor will the day since you are using a date field. :(

You say 105823 milliseconds but then convert it as Seconds. Assuming that your field is seconds, I would try converting it using MOD and INT for each part:

=RIGHT("00" & INT( Fields!TimeSinceLastReset.Value / 86400), 3) & ":" &
 RIGHT("0" & INT( (Fields!TimeSinceLastReset.Value MOD 86400) / 3600), 2) & ":" & 
 RIGHT("0" & INT(((Fields!TimeSinceLastReset.Value MOD 86400) MOD 3600) / 60), 2) & ":" & 
 RIGHT("0" & INT(((Fields!TimeSinceLastReset.Value MOD 86400) MOD 3600) MOD 60), 2)

The result is

001:05:23:43

86,400 is the seconds in a day and 3600 is seconds in an hour. It uses MOD to get the remainder from the previous calculation for each subsequent time field.

If you do not want the days, you can remove the MOD 86400.

You may need to divide your field by 1000 if it is milliseconds.

(Fields!TimeSinceLastReset.Value / 1000)
user_44596170
Related