Convert Milliseconds to Hours and Minutes String
SQL Server 2000: Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Date: 24 Jun 2010
This is another specialty function that I have used in the past to produce nicely formatted reports.
It converts the the number of milliseconds provided to the form '0013h 04m'
The selection of the number of leading zeros was purely to fit the report at the time. This can be easily changed by altering the last parameter of each RIGHT() function
I'm the first to admit that my VB skills are not as sharp as they could be, so I generally resort to formatting strings within the stored procedures that I use to feed my SSRS reports. I find this easier initially, and also it has been far easier to implement formatting changes as multiple reports do not need to be redeployed.
In this case, I chose milliseconds as the input for several reasons:
Milliseconds are the lowest common denominator for time measurement in SQL Server, so the conversion process is always the same
The period between any two datetimes can be converted to milliseconds using a single call to the built-in function DATEDIFF()