Convert Milliseconds to HH:MM:SS

I am writing a report that Queries a SQL DB using 'SQL Server Business Intelligence Development Studio'. I have a field in the DB called duration and it is in milliseconds. I am trying to find an easy way to convert the format from Milliseconds to HH:MM:SS.

Nearest I can get is the following for the field:

=Int(((Fields!DURATION.Value/1000) / 60) / 60) & ":" & Int(((((Fields!DURATION.Value/1000) / 60) / 60) - Int(((Fields!DURATION.Value/1000) / 60) / 60)) * 60)

The output is in HH:MM. One issue with this is if the MM is say :03, it prints as :3. I lose the leading 0 so 9:03 (9hrs and 3 minutes) prints as 9:3. Where as 9:30 (9 hrs and 30 minutes) prints as 9:30 as it should.

Is there an easier way to do this?

TIA...

Mike...

Try using the following expression, which uses the TimeSpan struct.

=TimeSpan.FromMilliseconds(Fields!DURATION.Value).ToString()

Ian|||

This is the code I use, using a custom code function. hope it will help

J

Shared Function FormatTime(ByVal Seconds As Integer) As String
Dim str As String
Dim hour As Integer
Dim min As Integer
str = ""
hour = Abs(Seconds\3600)
min = Abs((Seconds MOD 3600)/60)
If Seconds>= 0
str = iif(hour <= 9, "0" & CStr(hour), FormatNumber(hour, 0, , ,TriState.True)) & ":" & iif(min <= 9, "0" & CStr(min), CStr(min))
Else
str = "-" & iif(hour <= 9, "0" & CStr(hour), FormatNumber(hour, 0, , ,TriState.True)) & ":" & iif(min <= 9, "0" & CStr(min), CStr(min))
End If
return str
End Function

use like this

=Code.FormatTime( Fields!yourfeildname.Value)

|||

this worked good except the seconds come out as a long decimal (09:11:04.982345). How do I round that off. doesn't have to be exact, just need to get rid of the stuff to the right of the decimal.

thx....

|||

You should just be able to format the output, or use the format command to convert the displayed output to whatever you require ( see BOL )

J

|||Try using FromSeconds and convert the milliseconds to seconds whole seconds. Examples,

Without Rounding:
=TimeSpan.FromMilliseconds(Int(Fields!DURATION.Value/1000)).ToString()

or

With Rounding:
=TimeSpan.FromMilliseconds(CInt(Fields!DURATION.Value/1000)).ToString()

Ian