Skip to Content
0

how to query the time field from SAP Business One

Jun 14, 2017 at 01:20 AM

236

avatar image
Former Member

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)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Balaji Selvaraj Jun 14, 2017 at 05:07 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Jun 14, 2017 at 02:37 AM
0

Hi,

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

T0.[UpdateTS], T0.[CreateTS]

Regards,

Nagarajan

Share
10 |10000 characters needed characters left characters exceeded