Returning Time As String In A BAQ

  • 22 February 2024
  • 5 replies
  • 101 views
M
Userlevel 2

K22.1 on classic screens.

Because the Part Transaction History Tracker displays SysTime as an integer when it’s un-hidden from the grid, I’ve been asked to provide the transaction times in normal format.

Everything I’ve seen posted here and elsewhere in the user community, says to use the built in SQL function CONVERT; and specifically the documentation within calculated field builder says, when I click the TimeToString tool: convert(varchar, , 108) and the PartTran.SysTime field would go between the commas.  But the integer still appears in the BAQ designer, whether I format the display field as string or {time} it still shows incorrectly.

As of today, the EpicCare knowledge base article “13. How to convert time to HH:MM:SS format.” has two possibilities, one of which is the same type of CONVERT:  CONVERT( NVARCHAR(8), HDCase.LastUpdatedTime, 114) which has the same problem: it displays the integer number rather than a time value like 8:12:17 AM.

Their other option is a formula that fortunately WORKS!  It is:

CONVERT(NVARCHAR,HDCase.LastUpdatedTime / 3600) + ':' + RIGHT('0' + CONVERT(NVARCHAR,HDCase.LastUpdatedTime /60%60),2)+ ':'+RIGHT('0' + CONVERT(NVARCHAR,HDCase.LastUpdatedTime %60),2)

 

This gives military time, which I’m currently making the minor changes necessary to do the AM/PM. I don’t know what’s happened to the CONVERT function that used to provide human readable times, but I wanted to post this; hope this helps someone!

……….Monty.

5 replies

slepley
Userlevel 4

Hi Monty,

You also could just create a calculated field like the following:

 

Scott Lepley
M
Userlevel 2

Thanks Scott!  That gives…

1/1/1900 1:25:10 PM

So I just tried a substring function to peel off those first nine characters, and I get an error message saying DateTime is not valid for the first argument of the Substring function.  Even when I try to put it in a different calculated field, I get the same error message, and that’s explicitly declaring your suggested function as an nvarchar(16) type!  I don’t understand why this environment throws so many obstacles into a simple conversion from time format to a string.

Thanks for the suggestion though, it is a lot simpler than the only thing I’ve found so far that actually gives me a string.

..Monty.

M
Userlevel 2

Here’s the AM/PM version I put together.  HTH,

..Monty.

(case when CONVERT(NVARCHAR,(PartTran.SysTime / 3600)%12) = 0 then '12' else   CONVERT(NVARCHAR,(PartTran.SysTime / 3600)%12) end)  + ':' + RIGHT('0' + CONVERT(NVARCHAR,PartTran.SysTime /60%60),2)+ ':'+RIGHT('0' + CONVERT(NVARCHAR,PartTran.SysTime %60),2) + (case when CONVERT(NVARCHAR,PartTran.SysTime / 3600) < 12 then ' AM' else ' PM' end)

slepley
Userlevel 4

It seems like you have the wrong format selected.  You want {longtime} rather than {datetime}.

Scott Lepley
slepley
Userlevel 4

Appears like this…

 

Scott Lepley

Reply


Terms & Conditions