Convert Milliseconds to Hours and Minutes String

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: ChillyDBA

Date: 24 Jun 2010

Description

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()

Code

DROP FUNCTION [dbo].[udf_MillisecondsToHoursAndMinutesString]

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

RETURNS VARCHAR(10)

/***************************************************************

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'))