Convert milliseconds to hh:mm:ss.ms

Hi there I have a integer which is milliseconds representing a lap time

I would like to convert this to the hh:mm:ss.ms  format to show the lap time properly using MSSQL or even vb.net

can someone help
LVL 1
davomanAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Göran AnderssonConnect With a Mentor Commented:
The you need both the TimeSpan and DateTime value for the output, so that you can get the number of hours from the TimeSpan:

Dim s As TimeSpan = TimeSpan.FromMilliseconds(ms)
Dim t As DateTime = DateTime.Today + s

Dim display As String = Convert.ToInt32(Math.Floor(s.TotalHours)).ToString() + ":" + t.ToString("mm:ss.fff")
0
 
drydenhoggCommented:
Thinking around the problem slightly, instead of attempting to start dividing the number up etc, I would cheat it a bit.

Select DateAdd(ms,12345,0)

where 12345 is your number of ms, and 0 is considered the start date for SQL, (1/1/1900). Assuming no lap time exceeds a single day, discard the date part and display only the time.

One caveat is that times stored in SQL are not accurate to the MS, the accuracy is to  3.3 ms. 1 millisecond accuracy is not achievable using the datetime type.

hth.
0
 
jake072Commented:
This is easy, just use a TimeSpan!

Jake

Dim ts As New TimeSpan(0, 0, 0, 0, 62345)
Label1.Text = ts.ToString()

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Göran AnderssonCommented:
Create a DateTime value by combining any date (doesn't matter as we don't display it) and add the milliseconds:

Dim t As DateTime = DateTime.Today + TimeSpan.FromMilliseconds(ms)

or

Dim t As DateTime = DateTime.Today.AddMilliseconds(ms)

Now you can format the DateTime value the way that you want:

Dim display As String = t.ToString("HH:mm:ss.fff")


If you convert the value to a datetime value in the database, you loose precision, as drydenhogg mentioned.

If you convert the value to a TimeSpan, you can't control how it's converted to a string. The milliseconds are optional in the conversion, so they will not be displayed using a set number of digits as with the DateTime formatting.
0
 
jake072Commented:
GreenGhost,

Populating a DateTime value with a TimeSpan added is way less efficient then simply creating a TimeSpan.

It is unnecessary, as what if the race covered 24 hours+ ?  TimeSpan.ToString will yield identical results.

Jake
0
 
Göran AnderssonCommented:
> Populating a DateTime value with a TimeSpan added is way less efficient then simply creating a TimeSpan.

That is almost totally irrelevant, as 98% of the time is used to convert the value to a string anyway.

> TimeSpan.ToString will yield identical results.

Not at all.

With a DateTime value you can format it into a string that always has the same number of digits for fractional seconds. With a TimeSpan value that is not possible.
0
 
davomanAuthor Commented:
I am going to have to be able to deal with more that 2 hours ie a time could be

hh:mm:ss
25:03:12

So I need a method that will allow for this
0
 
jake072Commented:
GreenGhost,

I see what you mean how milliseconds aren't included if the number is a whole second.

Seems DateTime is a better way, sorry.

Jake
0
 
davomanAuthor Commented:
greenghost, that looks good and will still not be restricted by the 24 hour clock is that correct
0
 
Göran AnderssonCommented:
That is correct. The TotalHours property returns the TimeSpan value as hours, so if the TimeSpan contains 25:03:12, the TotalHours returns the value 25.053333333333333333. The Math.Floor method is used to round the value down to 25.0 to get only the hours.
0
 
davomanAuthor Commented:
actuall the 25:03:12 would be fine actually I won't need the floor, it will allow them to see their totalrace time

thanks I will check this and post acceptence
0
 
Göran AnderssonCommented:
Yes, but the minutes and seconds is created by formatting the DateTime value. If you don't use Math.Floor, you would get 25.0533333333333333333:03:12.
0
 
davomanAuthor Commented:
Sorry for the delay thanks a lot for the answer
0
All Courses

From novice to tech pro — start learning today.