Query for the last update time of a sales order

[former_member399138]

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?

[former_member185682]

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

[former_member399138]

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.

[former_member185682]

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

[former_member185682]

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