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’.
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
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
The easiest way one is to go to decimal hours by dividing by the seconds
per day. Not quite the same format but close.
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)
I only had to replace the HDCase.LastUpdatedTime to the field I was trying to convert - in this case ResourceTimeUsed.StartTime