Need to format HH:MM:SS

Question

Post reply

Need to format HH:MM:SS

Viewing 9 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

robin 31791 SSC Rookie Points: 46 More actions · Answer 1

I would be doing this in SSRS, and starting with seconds, as an integer. Thank you so much for your help!

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 2

I'll have to leave it to one of the others, thenm Robin. SSRS isn't my strong suite.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

robin 31791 SSC Rookie Points: 46 More actions · Answer 3

After much rummaging, I've found the answer here: http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/67660/

Here's the function for the report: =Code.GetDurationAsString(<fieldname>)

Here's the code:

Function GetDurationAsString(ByVal total As Double) As String

Dim hour As Integer

hour = Math.Floor(total / 3600)

Dim min As Integer

min = Math.Floor((total - (hour * 3600)) / 60)

Dim sec As Integer

sec = Math.Floor(total - (hour * 3600) - (min * 60))

Dim retThis As String

If hour > 999 Then

retThis = Microsoft.VisualBasic.Right("0000" + RTrim(CType(hour, String)), 4) & ":" & Microsoft.VisualBasic.Right("00" & RTrim(CType(min, String)), 2) & ":" & Microsoft.VisualBasic.Right("00" & RTrim(CType(sec, String)), 2)

ElseIf hour > 99 Then

retThis = Microsoft.VisualBasic.Right("000" + RTrim(CType(hour, String)), 3) & ":" & Microsoft.VisualBasic.Right("00" + RTrim(CType(min, String)), 2) & ":" & Microsoft.VisualBasic.Right("00" & RTrim(CType(sec, String)), 2)

Else

retThis = Microsoft.VisualBasic.Right("00" + RTrim(CType(hour, String)), 2) & ":" & Microsoft.VisualBasic.Right("00" + RTrim(CType(min, String)), 2) & ":" & Microsoft.VisualBasic.Right("00" & RTrim(CType(sec, String)), 2)

End If

Return retThis

End Function

Thank you Frank Fernando!!

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 4

I guess that might be yet another reason for me to dislike SSIS. It's a whole lot simpler in T-SQL.

SELECT CONVERT(VARCHAR(10),DATEDIFF(hh,0,DATEADD(ss,SecondsColumn,0)))

+ RIGHT(CONVERT(CHAR(8),DATEADD(ss,SecondsColumn,0),108),6)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 5

@robin - A very small change to my original post gives you what you want with significantly less code required. I've pasted it below.

Personally, I'd still be tempted to use TSQL (ie have 2 columns, one that has the seconds and the other with the formated value) but, if you're insisting on doing it in SSRS, try the following.

In the Code section of the Report (go to Report Properties), add the code below

Function secondsToString(seconds As Int64) As String

Dim myTS As New TimeSpan(seconds * 10000000)

Return String.Format("{0:00}:{1:00}:{2:00}:{3:00}", myTS.Days, myTS.Hours, myTS.Minutes, myTS.Seconds)

End Function

Then in your report item Textbox, edit the Expression and add

= Code.secondsToString(Fields!seconds.Value) keeping in mind that your field *will not* be called 'seconds', so change it appropriately.

This should return you a formated string of 00:00:19:08 (ie 0 days 0 hours, 19 minutes and 8 seconds) for the value 1148.

@jeff - i think you meant SSRS not SSIS, but heck, there's reasons to not like either on any given day :):hehe:

Steve.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Removed post.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

robin 31791 SSC Rookie Points: 46 More actions · Answer 7

Steve,

You're right, that is much shorter and more concise, thank you!! This will come in really handy for this company as they build contact center software, so much of what is reported on is time. Super-handy!!

Thanks again,

Robin

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 8

stevefromOZ (11/27/2011)
@jeff - i think you meant SSRS not SSIS, but heck, there's reasons to not like either on any given day :):hehe:

They're all 4 letter words to me. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 9

After the day I've had with SSIS today, i'm with you on that one!

Steve.