In the BAQ designer - How do I get a time that is in integer format or decimal into a HH:MM format?

ERP 10
1

I’m using the ResourceTimeUsed table - starttime (which is integer) or I can use the LoadHour which is a decimal format - but need it to display in HH:MM format

ie: ResourceTimeUsed.StartTime is 35460 >99,999
ResourceTimeUsed.LoadHour is 9.85 >9,99
Both are equal to 9:51am (time offset from Midnight value of 0)

I’ve tried creating a calculated field but not having any luck with the functions available to convert to format I need properly.

JobOper Table - fields are empty - so I guess after global scheduling the data is stored in the ResourceTimeUsed table since it is not ‘permanent’.

2

The first value is seconds since midnight a quick google search “SQL convert seconds to time” yields the code below as a solution

SELECT CONVERT(varchar, DATEADD(ms, 35460 * 1000, 0),114)

The second format is a decimal representation of an hour and a similar google search
“decimal hour to hour minute second SQL” yields the code below as a solution

SELECT  CAST(CONVERT(VARCHAR,DATEADD(SECOND, 9.85 * 3600, 0),108) AS TIME)

Google is your friend :grin:

1 Like
3

You can also convert Epicor seconds from midnight as a calculated field this way:
CONVERT(varchar, DATEADD(ss, [SECONDS SINCE MIDNIGHT VALUE], 0), 108)
I have had to use this many times to convert to HH:MM:SS
(you can encapsulate this in REPLACE to remove the “:”)
The 108 & 114 values are date/time formats
Here is a list of available formats: Date and Time Conversions Using SQL Server

1 Like
4

The easiest way one is to go to decimal hours by dividing by the seconds
per day. Not quite the same format but close.

Brad

5

Here is the solution provided by Epicor Support - and it worked.

How to convert time to HH:MM:SS format. Note for this example set the data type to nvarchar and the format to x(8).

CONVERT(VARCHAR,HDCase.LastUpdatedTime / 3600) + ‘:’ + RIGHT(‘0’ + CONVERT(VARCHAR,HDCase.LastUpdatedTime /60%60),2)+ ‘:’+RIGHT(‘0’ + CONVERT(VARCHAR,HDCase.LastUpdatedTime %60),2)

2 Likes
6

I only had to replace the HDCase.LastUpdatedTime to the field I was trying to convert - in this case ResourceTimeUsed.StartTime