THE SQL Server Blog Spot on the Web

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

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

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 )
    ELSE
        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.

@Jamiet

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

Comments

 

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

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement