Formatting times in SSRS

Kinetic ERP
1

Hello all
I wonder if someone can help me.

I am in the process of migrating our old Crystal reports to SSRS and am currently working on our Job Traveler.

I need to show the JobOper.ProdStandard value in hh:mm;ss

In crystal this is achieved using the formula
time({JobOper.ProdStandard}/24)

But I can’t find how to do this is SSRS

If anyone can help I’d be really grateful for your insight.

Thanks
Jane

2

try:

CStr(Format({JobOper.ProdStandard},"hh:mm:ss"))
3

Hi Clint

Thanks for the prompt reply. I used your formula, with the SSRS field name, so
=CStr(Format(Fields!ProdStandard.Value,“hh:mm:ss”))

But in the report I get the format string rather than the value

image

:woman_facepalming:

4

You may need to convert it to a TIME value first then as I recall that is a DECIMAL value…

2 Likes
5

Yep. Something along the lines of, casting 0 as time to produce a base time value of midnight (00:00:00), dateadd the operation time decimal value to that, then format the result into hh:mm:ss format.

Which will be returning a time of day, so this will do overflow things if any operation is ever estimated to run more than 24 hours. If that’s not definitely impossible then it’s time to do some clunky modulo things. IE, if your prodstandard is 1.51 something like:

cstr(cint(1.51)) & ":" & cstr(cint(1.51%1*60)) & ":" & cstr(cint((1.51*60)%1*60))

Casting as integer truncates the decimal values, if fractional seconds matter then round those. Get that figured out then hack in some zero-padding, something like

replicate("0", len(cstr(cint(1.51%1*60)))%2) & cstr(cint(1.51%1*60))

But most of all, don’t forget that ProdStandard isn’t hours. It’s a number of units, and the units are specified by StdFormat.

3 Likes