Convert Time Field to HH:MM:SS

[rholndoner (Randy Holndoner)]

Easy question for the early riser today! lol

Not sure why I’m having such problems with this, but I want to just take a simple field (ShipHead.ChangeTime), and convert it from “28189” to something legible.

I’m assuming somewhere within the calculated fields of a BAQ I can accomplish this?

[utaylor (Utah Taylor)]

Convert PartTrans.SysTime to actual time?

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. 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 …

Check this out.

It is two fields from PartTran called SysDate and SysTime and @hkeric.wci is using both to come up with a formatted datetime.

[rholndoner (Randy Holndoner)]

Stupid question…

Are these then put into a calculated field to display in a BAQ? I’m guessing I can do something similar since I can use ShipHead.ChangeDate and ShipHead.ChangeTime?

I only need the time, but whatever works I will take.

[mark.yates]

I have set up a calculated field with a Data Type of datetime and use the following calculation

convert(datetime, ((datediff(day,‘01/01/1900’ , ShipHead.ChangeDate))+(convert(decimal, ShipHead.ChangeTime)/86400)))

[utaylor (Utah Taylor)]

Yeah it would be in a calculated field.

[askulte (Andris Skulte)]

I use a quick nvarchar calculated field in the BAQ:

	CONVERT(varchar, DATEADD(ss, PartTran.SysTime, 0), 8)

Style 8 gives HH:MM:SS, while 14 or 114 gives HH:MM:SS:MMMM (milliseconds)

FYI, SysTime is seconds after midnight.

[rholndoner (Randy Holndoner)]

Thanks, Andris.

Don’t know why I had problems with doing this, but this worked on the first try.

[utaylor (Utah Taylor)]

Nice Andris, I am sure this post will be a favorite for years to come.