Need to format HH:MM:SS

Question

Post reply

Need to format HH:MM:SS

latingntlman

SSCarpal Tunnel

Points: 4348
More actions
May 22, 2008 at 1:49 pm

#186976

I'm creating a report in SSRS 2000 and one field reflects time spend on phone daily. However is being displayed as for example, OutboundHours 4818. How can I get this to display in the report as hh:mm:ss.
Is there code that I can plug into a variable or syntax that will accomplish this?
I don't want to convert from the sql code because in the report I'll need to create groupings with totals.
Any light will be appreciated.
John

Viewing 15 posts - 1 through 15 (of 24 total)

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

Loner SSC-Insane Points: 21279 More actions · Answer 1

You can find it under 'format' to convert it to HH:MM:SS.

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

guessing that you'd incorrectly named outboundhours and the number 4818 represents seconds (or possibly minutes) then some simple math, using modulo will let you pick out the hours, minutes and seconds from a number of seconds.

If you have actually named it correctly, and outboundhours == 4818, then your format will never be HH:MM:SS as you have no minutes nor seconds, only hours :crazy:

Steve.

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 3

I apologize. I believe it represents time in Seconds.

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

latingntlman (5/26/2008)
I apologize. I believe it represents time in Seconds.

I don't know too much about SSRS, yet... but here's how to do it in T-SQL...

SELECT CONVERT(CHAR(8),DATEADD(ss,4818,0),108)

Replace the 4818 with the column name and add a FROM clause...

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

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

p.s. That will work only up to 23:59:59... if you need to exceed 24 hours, post back... there's a way...

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

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 6

Thx, I believe it may exceed 24 hrs over the course of months.

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 7

Very similiar in MSRS:

[Code]

=FormatDateTime(DateAdd("s",Fields!OutboundHours.Value,0),"HH:mm:ss")

[/Code]

Here's some references for you:

http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx

http://msdn.microsoft.com/en-us/library/aa262710(VS.60).aspx

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

Dave... anything in MSRS for over 24 hours?

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

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 9

You'd have to format the hours yourself. I'd have to try it out, but something like:

= Format(Fields!OutboundHours.Value / 3600, "#")

+ ":"

+ FormatDateTime(DateAdd("s",Fields!OutboundHours.Value,0),"mm:ss")

should work. Didn't check the syntax. Hopefully you get the idea.

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 10

I tried syntax =FormatDateTime(DateAdd("s",Fields!OutboundHours.Value,0),"HH:mm:ss")

and the report displays #Error for that field and my Output window says:

Build complete -- 0 errors, 0 warnings

The value expression for the textbox ‘OutboundHours’ contains an error: Argument 'DateValue' cannot be converted to type 'Date'.

Preview complete -- 0 errors, 1 warnings

The field OutboundHours in the SQL Db table is Integer type.

As for converting it in SQL I don't want to do that because on the Report side I'll need to add groupings and running totals.

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

Personally, I'd still be tempted to use TSQL (ie have 2 colimns, one tha 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:G}:{1:G}:{2:G}:{3:G}", 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 tha your field *will not* be called 'seconds', so change it appropriately.

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

You can mod the formating as much as you like (e.g. add 'D', 'H', 'M' and 'S' behind each integer to give the user some hint as to what they're seeing, do this in the function (e.g. "{0:G}D:{1:G}H:{2:G}M:{3:G}S" will produce 0D:0H:19M:8S for the example given above).

Upside, this does (or should do) exactly what you want. Downside, you'll need to add the code snippet to each report you want to use this in.

HTH,

Steve.

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 12

Steve,

I think I agree with you on having the values twice in the sql code. I only thought of it just this morning, but I'll still try to do it in the report just for experience.

Thanks a bunch.

John

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

This is great! Just what I've been looking for! One small question - how would I make it pad with a zero for any of the pieces, if the number were not greater than 9? For example, how would I make it format as follows: 00:09:45:03 ?? DD:HH:MM:SS, in other words...

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

robin 31791 (11/25/2011)
This is great! Just what I've been looking for! One small question - how would I make it pad with a zero for any of the pieces, if the number were not greater than 9? For example, how would I make it format as follows: 00:09:45:03 ?? DD:HH:MM:SS, in other words...

For SSRS or T-SQL? And what are you starting with? Seconds or ?

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