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.

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 |

Here is yet another way for a BAQ calculated field

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

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

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.

```
// 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)
```

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?

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`