Convert Epicor time

Epicor stores time as a decimal formatted >9.99. Is there any way in SSRS to convert this so it shows like 1:30:00 PM?

Format the text box that you are putting the field in to be Time.

That looks more like number representing some amount of time (like 10.23 minutes), and not a time of day.

Are you trying to convert an amount of time to an actual time of day?

I already tired that and SSRS returned an error.

The LaborDtl.ClockInTime is a decimal formatted as >9.99. So the data displays as 13.80 for 1 PM and 80 /100 of an hour for the minutes (I believe).

If it is in a BAQ, you could make it a calculated field of type datetime, and use a formula like:

dateadd(second, LaborDtl.ClockinTime*3600, convert(datetime, LaborDtl.ClockInDate))

Then in the format filed select {longtime}

You’ll get

image

That doesn’t seem to work in SSRS. Error says Convert is not is a type and cannot be used as an expression

Have you tried the Text Box Properties? Just add the field to a text box (no convert) and right click.

image

image

That was what you would use in a BAQ (which uses SQL syntax).

If you’re doing it in an expression in an RDL field, try:

=Format(Floor(iif(LaborDtl.ClockinTime>= 13, LaborDtl.ClockinTime-12, LaborDtl.ClockinTime)),"00")&":" & Format((LaborDtl.ClockinTime-floor(LaborDtl.ClockinTime))*60,"00") & iif(LaborDtl.ClockinTime >= 13, " PM", " AM")

I found this on another site.

=int(Fields!ClockinTime.Value)&":"& int((Fields!ClockinTime.Value -int(Fields!ClockinTime.Value))*60) &":"& ((Fields!ClockinTime.Value -int(Fields!ClockinTime.Value))*60)*60-int((Fields!ClockinTime.Value -int(Fields!ClockinTime.Value))*60)*60

I add TimeValue to it as follows and it works.

=TimeValue(int(Fields!ClockinTime.Value)&":"& int((Fields!ClockinTime.Value -int(Fields!ClockinTime.Value))*60) &":"& ((Fields!ClockinTime.Value -int(Fields!ClockinTime.Value))*60)*60-int((Fields!ClockinTime.Value -int(Fields!ClockinTime.Value))*60)*60)