answered Oct 15, 2009 at 09:08 AM
I chose to keep the calculation to seconds to preserve accuracy but then CHOP off the unneeded seconds. Also, if you can bend your requirement to include hh:mm:ss then you can eliminate the LEFT function that I used to drop the seconds.
--here is the answer
--here is an end-to-end working example
--add some values
--view the records with the time difference
--clean up from our test
answered Oct 15, 2009 at 01:22 PM
I log elapsed processing time for an application's calculation, which allows me to have a ms precision of the 24 day: 86399999ms on a 64-bit machine.
Working with TIME in SQL 2008 is markedly easier than SQL 2000.
Because this is up to ms precision the rounding off (last 3ms) sets the largest lower boundary value to 997ms.
To trudge through TIME calculations manually, consider the TSQL script below, which gives you a nice result in either CHAR(12) or TIME to the last ms, i.e. 999. Don't ask me why I had the energy to write this piece of code...
answered May 21, 2010 at 07:44 AM