on 01-15-2017 8:43 PM
Hey,
I am trying to create a query in sql to retrieve the last update date and time of a sales order. I did something similar for another query with CreateDate and use the DocTime to determine the time the sales order was created. What column could I use in the ordr table (or any other table) that would give me the Time of the last update for the sales order?
Hi Michael,
Use the field UpdateTs to get the time of last update. A sample below:
SELECT
CreateDate,
FORMAT(CreateTS, '##:##:##'),
UpdateDate,
FORMAT(UpdateTS, '##:##:##')
FROM
ORDR
Hope it helps.
Kind Regards,
Diego Lother
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hey Diego,
Thanks for help. However, I forgot to mention this is SAP 8.81 and runs on SQL Server 2008 which does not contain the Format function. Also, that query you provided, wouldn't that always return 00:00:00 because by default I see all the Datetimes in CreateDate and UpdateDate as 00:00:00 which I think is by default.
Hi Michael,
The SAP B1 stores the time in a separate field CreateDate -> CreateTS, UpdateDate -> UpdateTS.
On the datetime fields you always see 00:00:00.
The format of field CreateTS and UpdateTs is the following HHmmss, without ':'. The format function is just to left your result more elegant, however you can use other available function on your sql server version. Only remember, this fields are not date time field they are an int field. Case this field UpdateTS and CreateTS aren't available on version 8.81 I don't have other approach.
Hope it helps.
Kind Regards,
Diego Lother
Hi Michael,
You can use the function this to format.
SELECT
CreateDate,
CASE WHEN LEN(CreateTS) = 5 THEN STUFF(STUFF(CreateTS,2,0,':'),5,0,':') ELSE STUFF(STUFF(CreateTS,3,0,':'),6,0,':') END AS CreateTS,
UpdateDate,
CASE WHEN LEN(UpdateTS) = 5 THEN STUFF(STUFF(UpdateTS,2,0,':'),5,0,':') ELSE STUFF(STUFF(UpdateTS,3,0,':'),6,0,':') END AS UpdateTS
FROM
ORDR
Hope it helps.
Kind Regards,
Diego Lother
User | Count |
---|---|
93 | |
11 | |
10 | |
6 | |
5 | |
5 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.