Date and Time - 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        


Author:    ChillyDBA
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()


DROP FUNCTION [dbo].[udf_MillisecondsToHoursAndMinutesString]

CREATE FUNCTION [dbo].[udf_MillisecondsToHoursAndMinutesString]  (@Milliseconds  INT)

Purpose:   Returns a string in the format 0h 0m for an input of #milliseconds

Author:        ChillyDBA
History:   24 Jun 10 - Initial Issue

BEGIN --function def

DECLARE @TimeString      VARCHAR(10)

SELECT @TimeString =  RIGHT('0000000000' + CAST((@Milliseconds / 3600000) AS VARCHAR(10)) + 'h ', 6)
RIGHT('0000000000' + CAST(((@Milliseconds % 3600000) / 60000) AS VARCHAR(10)) + 'm',3)

RETURN (@TimeString)

END --function def

-- SELECT [dbo].[udf_MillisecondsToHoursAndMinutesString](1234567)  
-- SELECT [dbo].[udf_MillisecondsToHoursAndMinutesString](12345678)
-- SELECT [dbo].[udf_MillisecondsToHoursAndMinutesString](DATEDIFF(ms, '1 jan 2012 01:00:00','2 jan 2012 03:13:00'))

Andy Hughes,
Jun 22, 2012, 11:36 AM