P&D Boyum SQL Function & MS SQL FORMAT Function
Hello experts.
Basically, what is the normal or recommended way to get the date & time field into a single result and then convert that to UTC when the service call time fields (table OSCL) don't record seconds like the marketing document tables time fields do?
I am attempting to create a Boyum P&D Report Action where the email body uses the Boyum SQL function. I need to get the service call's Respond By Date or the created date converted from our DB's local (EST) time to UTC in the format of "MM/DD/YYYY HH:mm:ss".
Boyum returns the error message of "Incorrect syntax near the keyword 'CONVERT'.". I've tried the MS SQL "FORMAT()" function with GETDATE() and the same error message is returned.
When I run the SQL directly in the DB or the SAP query editor, I don't have any issues.
We are running SQL Server and I am using the FORMAT function. I don't understand how that function could be an issue as MS SQL has supported it since SQL Server 2012.
My Boyum SQL can be found below and screenshot of the Report Action attached.
<FSETA>SQL(FORMAT(CONVERT(DATETIME, COALESCE(respByDate, U_SII_CCcreateDate, createDate)+' '+FORMAT(1700, '##:##')) AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'UTC', 'MM/dd/yyyy HH:mm:ss'))</FSETA>
Thank you in advance for any assistance you can provide.

-
I was able to solve this issue. Changed FORMAT(1700, '##:##') to FORMAT(1700, '00:00'). I haven't researched why this matters yet but will do so soon.
Iniciar sesión para dejar un comentario.
Comentarios
1 comentario