Convert PartTrans.SysTime to actual time?

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
6 Likes

Here is yet another way for a BAQ calculated field

CONVERT(varchar, DATEADD(ss, Constants.CurrentTime, 0), 108)

6 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!

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…

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 … :wink:

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

image

I do prefer the simple division, shorter syntax. :slight_smile: 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 :slight_smile:

In this case division should suffice.

1 Like
// 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)
1 Like

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?
:crazy_face:

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