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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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
Göran AnderssonCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.