Skip to Content

how to query the time field from SAP Business One

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

erererererer.png (21.3 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jun 14, 2017 at 05:07 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 14, 2017 at 02:37 AM

    Hi,

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

    T0.[UpdateTS], T0.[CreateTS]

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded