Sunday, July 31, 2011

Convert Minutes to hh:mm in SQL Server Reporting Services

After running a DATEDIFF in Transact SQL to get the number of hours worked in a particular period, there was a requirement to convert that hours into hh:mm format.
In SQL Server Reporting Services there is the ability to write custom functions in VB.NET for your SSRS reports by just going to properties in the report and clicking the Code tab.

Below is the function I wrote.

Public Function ConvertToTimeFormat(DecimalValue as Decimal) As String

Dim hourValue as Decimal
Dim minuteValue as Decimal

hourValue = Int(DecimalValue)
minuteValue = (DecimalValue - hourValue) * 60

Return hourValue.ToString() + ":" + Int(minuteValue).ToString().PadLeft(2,"0")
End Function

So in my report cells i'm referencing my new function using the Code object.


Wallah, you're done.