Is there a formula to convert the PartTrans.SysTime to actual time stamp?
Itâs seconds from midnight, divide the value by 3600 and you have the hours in decimal form. 7.5 = 7:30am.
2 Likes
Thanks Jeff. That did the trick. However, it cutoff the minutes when i divided by 3600. After playing around i was able to find a formula to give me exact time:
formula : Ice.StringTime(PartTran.SysTime, âHH:MM:SSâ)
| Sys Date | Sys Time | time / 3600 | time2 |
|---|---|---|---|
| 5/14/2018 | 50584 | 14.00 | 14:03:04 |
| 5/14/2018 | 50935 | 14.00 | 14:08:55 |
| 5/21/2018 | 50026 | 13.00 | 13:53:46 |
| 5/9/2018 | 53076 | 14.00 | 14:44:36 |
| 5/17/2018 | 24716 | 6.00 | 06:51:56 |
| 5/17/2018 | 25097 | 6.00 | 06:58:17 |
| 5/17/2018 | 25826 | 7.00 | 07:10:26 |
| 5/23/2018 | 25548 | 7.00 | 07:05:48 |
| 5/23/2018 | 46917 | 13.00 | 13:01:57 |
8 Likes
Here is yet another way for a BAQ calculated field
CONVERT(varchar, DATEADD(ss, Constants.CurrentTime, 0), 108)
10 Likes
Just in case anyone else comes across this post, I tried Markâs solution but I still had my calculated field type set to datetime. This will cause it to show the year/month/date even if you just want time. Make sure you use nvarchar for the field type.
Thanks for the helpful formula Mark!
1 Like
Tripped across this SQL behavior that drove me nuts all day regarding converting seconds from midnight to decimal hours:
PartTran.SysTime / 3600
Ex: 50584 / 3600 = 14.00
Notice that it always resulted in an integer, the decimals were always zero.
I tried converting both sides to a decimal and all sorts of things, the calc field was even set to be Deciamal,what got me the proper Decimal Hour precision was:
PartTran.SysTime / 3600.00
Ex: 50584 / 3600 = 14.05
Just in case anyone else gets trappedâŚ
1 Like
Itâs a bit of a sql trap. int/int returns int. Basically, takes the highest precision part (numerator or denominator) of the sum as the type.
If you have int/decimal or decimal/int, it should return decimal.
If you have float/decimal or decimal/float, it will return float.
If you had done the following, it should also have worked though:
cast(PartTran.SysTime as decimal) / 3600
Should be the same using convert as cast
1 Like
Any reason not to just call Ice.StringTime(erp.PartTran.Systime,ââ") in your opinion?
@John_Mitchell
Sorry for the confusion, my reply wasnât about the validity of ice.StringTime(), nor PartTran.SysTime, but rather the SQL behavior, just to make others aware so no one hurts their head on a wall.
In my case I was working with Constants.CurrentTime / 3600 and I couldnât figure out why I kept getting a whole number back, even though the Calc field type was set to Decimal, I guess I assumed it was Casting it behind the scenes instead of formatting it.
Results when Time is 11:02AM:
Ice.StringTime(Constants.CurrentTime, ââ) = 11:02
Constants.CurrentTime / 3600.00 = 11.04
cast(Constants.CurrentTime as decimal) / 3600 = 11.04
(@AndrewM yes, casting either value would work as well)
11.04 is what I am going for, as I need the time in Decimal Hours Format and I didnât think that StringTime() had a format code for Decimal Hours.
Well⌠it does have âStringâ right in the function name, and doesnât take a string as the main parameter ⌠
Then there is alwaysâŚ
SELECT
FORMAT(DATEADD(SECOND, PartTran.SysTime, CAST(PartTran.SysDate as DateTime)), 'HH.mm') as time1,
DATEADD(SECOND, PartTran.SysTime, CAST(PartTran.SysDate as DateTime)) as time2,
*
FROM Erp.PartTran
I do prefer the simple division, shorter syntax. 
However if you use DATEADD with something like PayHours it would honor 24 and + and actually get you the âcorrectâ clock out date as well. There is a time and a place for everything
In this case division should suffice.
3 Likes
// Currently Elapsed Seconds Since Midnight
Convert.ToInt32( ((TimeSpan) DateTime.Now.Subtract( DateTime.Today )).TotalSeconds)
// Convert Epicors Time like NeedByTime or SysTime to Human Readable
DateTime.Today.AddSeconds( MtlQueue.NeedByTime ).ToString("HH:mm:ss"); // ex: 16:22:10
DateTime.Today.AddSeconds( MtlQueue.NeedByTime ).ToString("hh:mm:ss tt"); // ex: 04:22:10 pm
// ABL
string(MtlQueue.NeedByTime, "HH:MM:SS")
// SQL
convert(varchar(8), dateadd(second, MtlQueue.NeedByTime, 0), 108)
2 Likes
Crap! LOL! I didnât mean to stir up all this interest on an old post.
Where were yaâll 3 years ago?
Anyone else have any ways to play with Time Formats?
1 Like
sure!
public static string GetTimeSince(DateTime objDateTime)
{
// here we are going to subtract the passed in DateTime from the current time converted to UTC
TimeSpan ts = DateTime.Now.ToUniversalTime().Subtract(objDateTime);
int intDays = ts.Days;
int intHours = ts.Hours;
int intMinutes = ts.Minutes;
int intSeconds = ts.Seconds;
if (intDays > 0)
return string.Format("{0} days", intDays);
if (intHours > 0)
return string.Format("{0} hours", intHours);
if (intMinutes > 0)
return string.Format("{0} minutes", intMinutes);
if (intSeconds > 0)
return string.Format("{0} seconds", intSeconds);
// let's handle future times..just in case
if (intDays < 0)
return string.Format("in {0} days", Math.Abs(intDays));
if (intHours < 0)
return string.Format("in {0} hours", Math.Abs(intHours));
if (intMinutes < 0)
return string.Format("in {0} minutes", Math.Abs(intMinutes));
if (intSeconds < 0)
return string.Format("in {0} seconds", Math.Abs(intSeconds));
return "a bit";
}
Even better turn this into a DateTime extension!
Wouldnât that exit the function on the first return ? Such that any value of objDateTime that isnât between 12:00 midnight and 01:00 AM would only return the hours.
GetTimeSince(< a datetime that is 13h 22m 44s ago >) would return the string 13 hours
where
GetTimeSince(< a datetime that is 00h 22m 44s ago >) would return the string 22 minutes
Couldnât tell you. Using it for SMS from Epicor via rest seems to be working for me. Boosted it from the internet. He asked if anyone had more #copypaste
Adding to this for converting a systime to human HH:MM:SS in BAQâs with a quick nvarchar calculated field (so I can find it again!):
CONVERT(varchar, DATEADD(ss, PartTran.SysTime, 0), 8)
Style 8 gives HH:MM:SS, while 14 or 114 gives HH:MM:SS:MMMM (milliseconds)
1 Like
Additionally, if you have a separate date and time fields that you want to combine into one, then you can create a calculated field like this with the Data Type set to datetime:
DATEADD(ss, BookRel.BookTime, cast(BookRel.BookDate AS nvarchar))
to get a result like: 08/04/2023 09:46 AM
3 Likes