Convert a value to datetime type and format of HH:MM

[rikpage]

How to convert a value of 938 declared as a smallint to 09:38 as a datetime type?

To give you the complete set-up that I would like

SELECT T3.DocNum AS 'Goods Receipt #', T0.DocNum AS 'Sales Order #', T0.DocDate AS 'SO Date', (T1.LineNum +1) As 'Row #', T1.Quantity AS 'Ordered', T1.DelivrdQty AS 'Delivered', T1.OpenQty AS 'Open Qty', T1.ItemCode AS 'Item Code', T1.Dscription AS 'Description' 
FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry, PDN1 T2 INNER JOIN OPDN T3 ON T2.DocEntry = T3.DocEntry 
WHERE T1.[LineStatus] = 'O' 
AND  T0.[U_DeliveryCount] > 1 
AND  T1.[ItemCode] =  T2.[ItemCode] 
AND T1.[U_QtePrelever] = 0
AND (
Declare @SAPTime Int    
Declare @SQLTime datetime    
CASE 
WHEN T0.DocTime < '999' THEN  @SAPTime = ((LEFT((T0.DocTime),1)*60) + (RIGHT((T0.DocTime),2))) FROM OPCH T0 where T0.DocEntry = 1
WHEN T0.DocTime > '999' THEN  @SAPTime = ((LEFT((T0.DocTime),2)*60) + (RIGHT((T0.DocTime),2))) FROM OPCH T0 where T0.DocEntry = 1
END
SELECT @SQLTime = CONVERT(CHAR(8),DATEADD(MI,@SAPTime,'20000101'),108)
SELECT DATEDIFF(minute,CONVERT(VARCHAR(5), GETDATE(), 108),@SQLTime)
)<'10'
 

But I get the following errors

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'Declare'.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'CASE'.
Msg 137, Level 15, State 1, Line 14
Must declare the scalar variable "@SQLTime".
Msg 137, Level 15, State 2, Line 15
Must declare the scalar variable "@SQLTime".


Thx for the help!!

[ck9663]

In your sample, is it a straight forward inserting of colon to make it a time value? Or you need to convert to minutes? 938 = 09:38. So there are no 960 value? If yes, then convert to string and insert the colon.

Happy Coding!!!

~~ CK