how to query the time field from SAP Business One

[former_member280812]

hi expert,

I have query that will get the create time and update time from sap business one using MS SQL. My query is working but the problem is the result of the time field is returning int number format instead of time format. But when i test my query inside the sap b1 query manager the time result is returning correct. Can someone help me how to do this using MS SQL.

Please check the attached image.

Regards,

Eric

[former_member312729]

Hi Aucim,

The field is of integer in sql you need to convert it into time format.

example :

select T0.CreateTS from OINV T0 where T0.DocNum= '303'

result :123100

DECLARE @stringTime varchar(6) SET @stringTime = RIGHT('000000' + CAST((select T0.CreateTS from OINV T0 where T0.DocNum= '303') AS VARCHAR), 6) SELECT CAST(LEFT(@stringTime, 2) + ':' + RIGHT(LEFT(@stringTime, 4), 2) AS TIME) as TimeValue

Regards:

Balaji.S

[kothandaraman_nagarajan]

Hi,

There is two new fields two get update time and creation time,

T0.[UpdateTS], T0.[CreateTS]

Regards,

Nagarajan