Service call module update date and create time into UTC format of MM/dd/yyyy HH:mm:ss query

[ES_NB]

Hello experts.

I am needing to get the service call module update date & time (table OSCL) into a single column in the format of 'MM/dd/yyyy HH:mm:ss' and then have it converted to UTC. Ultimately, I will be using this query in a Boyum P&D Report Action. I have seen posts before (but cannot find now) where the SQL function STUFF() was used but I know that each of those examples were for marketing document tables. Please direct me to those posts if I am mistaken.

The OSCL table does not record time with seconds, therefore the max number in characters/numbers in the time fields will be 4 (i.e., 1344 for 13:44, instead of ORDR's 134423 for 13:44:23).

I am able to perform the query below in MS SQL as well as the SAP B1 query editor when the time field has at least 3 characters (e.g., on or after 1am) but the FORMAT function fails when it is less than 3.

Is possible to query for the update date & time (or the create date & time) into a single column without having to perform a case statement to evaluate the length of time field (which could be from 1 - 4 characters long [2 for 00:02 and 932 for 09:32 and 1748 for 17:48])? 

FORMAT(CONVERT(DATETIME, CONVERT(VARCHAR(10), OSCL.UpdateDate, 101)+' '+FORMAT(OSCL.UpdateTime, '##:##')) AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'UTC', 'MM/dd/yyyy HH:mm:ss')

 Thank you in advance for any assistance you can provide. 

[inga_babco2 · Accepted solution]

Hi,

You just need to change the format from '##:##' to '00:00':

FORMAT(CONVERT(DATETIME, CONVERT(VARCHAR(10), OSCL.UpdateDate, 101)+' '+FORMAT(OSCL.UpdateTime, '00:00')) AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'UTC', 'MM/dd/yyyy HH:mm:ss')

[ES_NB · Accepted solution]

WOW! That worked. Thank you very much. I'll read up on FORMAT function again.

[LoHa]

Hi

try this

SELECT
  OSCL.UpdateDate,
  OSCL.UpdateTime,

  RIGHT('0000' + CAST(OSCL.UpdateTime AS VARCHAR), 4) AS ZeitString,
  
  
  CAST(
    CONVERT(VARCHAR(10), OSCL.UpdateDate, 120) + ' ' +
    LEFT(RIGHT('0000' + CAST(OSCL.UpdateTime AS VARCHAR), 4), 2) + ':' +
    RIGHT(RIGHT('0000' + CAST(OSCL.UpdateTime AS VARCHAR), 4), 2) + ':00'
    AS DATETIME
  ) AS DatumTime

FROM OSCL
WHERE CallID = 1

regards Lothar