First of all, I recommend NOT formatting data for storage. It's just wrong for so many reasons.
If the data is ultimately bound for storage in a table, then do the following:
If you truly need it for display purposes, you'll find an overwhelming majority of people that will say that the best place to do such formatting is in the GUI or reporting app that you're using and I totally agree with that.
Sometimes, you've gotta do what you're gotta do (although you still need to follow the idea that it's almost never a good idea to store formatted data except in temporary objects). With that, this will produce the formatted output you desire.
SELECT CONVERT(CHAR(8),DATEADD(ms,526.30*60000,0),108) --24 hour time or duration
In the code above, the "Date Serial Number" of "0" has been used. "0" is the base date of "1900-01-01' . Multiplying your number by 60,000 converts your number to milliseconds. Adding that number of milliseconds to the base date creates an actual DATETIME datatype containing a date of 1900-01-01 with the correct time in hours, minutes, seconds, a milliseconds with a resolution of 3.3 milliseconds. The CONVERT uses format # 108 to format that date and time in the 24 hour format of hh:mi:ss and dumps that to a CHAR(8) datatype for display purposes.
For more information on formatting dates and times (which, again, is usually a mistake), please see the following MS Documentation. I strongly recommend that you become very familiar with all that's covered in the documentation because it covers a great deal more than just dates and times and you can actually pull off small miracles using CONVERT in conjunction with other functions.
I also recommend that you become insanely familiar with the other date functions and datatypes because just about everything that people do in SQL Server uses some form of date/time reference. I'll let you Google for those because, unless you've download the help-system (referred to as "Books Online" or just "BOL") to have such documentation available at your finger tips, it's a quintessential skill that folks like us need to become very good at.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)