Convert Epicor time

Epicor ERP 10
1

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?

2

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

3

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?

4

I already tired that and SSRS returned an error.

5

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

6

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

1 Like
7

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

8

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

image

image

9

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

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)

11

Any ideas on how to do this in a BPM?

12

It’s basically the same Elvin. An SQL datetime is the integer number of days since the epoch (1/1/1900 I think) and the time is the fraction of the day. Zero is midnight, 0.5 is noon, 0.75 is 6:00 PM, etc. If you take the seconds given and divide by the seconds in the day to get the fraction, you can add that to the date to get the DateTime that Microsoft likes.

2 Likes
13 
TimeSpan timespan = TimeSpan.FromHours(2.75);

From there you can break it out as you need.

4 Likes