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")
So in my report cells i'm referencing my new function using the Code object.
Wallah, you're done.