THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

Convert an integer seconds value into words in VBScript : Reporting Services

Occasionally I stick random bits of code up on this blog because (a) I know I’ll need it again someday and (b) maybe it’ll be useful for someone else. This particular VBScript function called TimeInWords can be used in SQL Server Reporting Services to turn a numerical value representing number of seconds into a meaningful string. For example:

  • TimeInWords(53) returns “53s”
  • TimeInWords(1632) returns “27m 12s”
  • TimeInWords(7923) returns “2h 12m 3s”
  • TimeInWords(894823) returns “10d 8h 34m 43s”

Pretty useful for reporting on durations. I haven’t done endless testing on it but I’m pretty sure it works. Here’s the code:

Function TimeInWords (RemainderTimeInSeconds as Integer) AS String
    Dim ReturnValue As String
    IF (RemainderTimeInSeconds > 86400)
        ReturnValue = TimeInDays(RemainderTimeInSeconds )
    ELSE IF (RemainderTimeInSeconds > 3600)
        ReturnValue = TimeInHours(RemainderTimeInSeconds )
    ELSE IF (RemainderTimeInSeconds > 60)
        ReturnValue = TimeInMinutes(RemainderTimeInSeconds )
        ReturnValue = TimeInSeconds(RemainderTimeInSeconds )
    End If
    Return ReturnValue
End Function

Function TimeInDays (RemainderTimeInSeconds AS Integer) AS String
    Return CStr(Floor(RemainderTimeInSeconds / 86400)) + "d " + TimeInHours(RemainderTimeInSeconds Mod 86400)
End Function
Function TimeInHours (RemainderTimeInSeconds AS Integer) AS String
    Return CStr(Floor(RemainderTimeInSeconds / 3600)) + "h " + TimeInMinutes(RemainderTimeInSeconds Mod 3600)
End Function
Function TimeInMinutes (RemainderTimeInSeconds AS Integer) AS String
    Return CStr(Floor(RemainderTimeInSeconds / 60)) + "m " + TimeInSeconds(RemainderTimeInSeconds Mod 60)
End Function
Function TimeInSeconds (RemainderTimeInSeconds AS Integer) AS String
    Return CStr(RemainderTimeInSeconds) + "s"
End Function

Nothing particularly special but if it saves someone else from writing it well…why not!

Hope that is useful to someone.


Published Sunday, November 14, 2010 11:36 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



AaronBertrand said:

Hey Jamie, this looks like VBScript when you don't use the recommended dbo (or other schema) prefix.  :-)

November 15, 2010 12:30 AM

Leave a Comment


This Blog


Privacy Statement