Time formatting

[telehort]

Trying to format a value (total seconds) into hh:mm:ss in MSRS and cannot find out how to do this, I would have assumed this would be an easy formatting change, but I cannot find any info on how to do this. Any ideas appreciated.

 

[Catadmin]

You would probably format it with an IIF statement in the Expressions for your textbox.

Not sure about how to format, but that's the best place I can think to do it.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[xlbo]

You will need to convert your value in seconds into an actual time value - this is not that straightforward within RS. If possible, I would advise you to do this conversion within SQL - something like:

select convert(char(8),dateadd(second,@Seconds,0),14)

Where @Seconds holds the value you need to convert.

should do the trick

If this is not possible, I think I have some code to do it but will need to search my reports to find it....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[telehort]

thank you for the reponse xlbo, since the values in the reports are ever changing every time we run the report I wouldn't know what the @seconds would be each time so I guess I am looking so that when the reports run, the value shows in hh:mm:ss instead of coming out in seconds and then having to convert it. Not sure if that makes sense or not, and maybe I wasn't clear in my first question. Kinda just trying to figure out how to "format a cell" like in excel where the output in that cell would be in hh:mm:ss. thanks

 

[Catadmin]

Hey, now there's an idea, Geoff! I wasn't thinking about that.

Telehort, if your values are coming out of a Stored Procedure, all you have to do is convert the data in your Select statement using Cast/Convert and some concatenation.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[xlbo]

Telehort - the @seconds would be used in a stored procedure or you can simply sub it for a field in your query.

In terms of "formatting the cell", you cannot really do that in this instance - wouldn't work in excel like that either - you will need to convert the value and then format it wherever you arte doing it - in the query or in the report...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MisterMo]

try this one

=cstr((((Fields!TotalSeconds.Value\60)\60)\24)) + ":" +
cstr((((Fields!TotalSeconds.Value\60)\60) mod 24 ))+ ":" +
cstr(((Fields!TotalSeconds.Value\60)mod 60)) + ":" +
cstr((Fields!TotalSeconds.Value mod 60 ))

-Mo