Time Difference In Query

Posted By mcolwell over 9 years ago

Does anyone have the syntax on how to perform a time difference in a Sage Query?

Here is an example query:

SELECT "SO_SalesOrderHistoryHeader"."SalesOrderNo", "SO_SalesOrderHistoryHeader"."CustomerNo", "SO_SalesOrderHistoryDetail"."ItemCode", "SO_SalesOrderHistoryDetail"."QuantityShipped", "SO_SalesOrderHistoryHeader"."LastInvoiceDate", "SO_SalesOrderHistoryHeader"."UDF_TIMEIN", "SO_SalesOrderHistoryHeader"."UDF_TIMEOUT"

FROM "SO_SalesOrderHistoryHeader" "SO_SalesOrderHistoryHeader", "SO_SalesOrderHistoryDetail" "SO_SalesOrderHistoryDetail"

As you can see, we have a TimeIn and a TimeOut field. Is there any way to return another column that calculates the time difference? If so, what would the syntax of that be?

Thanks!

0 jcnichols over 9 years ago

HI,

Q1: Are "SO_SalesOrderHistoryHeader"."UDF_TIMEIN", "SO_SalesOrderHistoryHeader"."UDF_TIMEOUT" both date types?

Q2: What value interval do you want the difference returned in? Years, Days, Hours, Minutes

John Nichols

Sage
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 jcnichols over 9 years ago in reply to jcnichols

Interval In Hours

{fn TIMESTAMPDIFF(SQL_TSI_HOUR, SO_SalesOrderHistoryHeader.UDF_TIMEIN, SO_SalesOrderHistoryHeader.UDF_TIMEOUT)}

Interval in Minutes

{fn TIMESTAMPDIFF(SQL_TSI_MINUTE, SO_SalesOrderHistoryHeader.UDF_TIMEIN, SO_SalesOrderHistoryHeader.UDF_TIMEOUT)}

More Info

msdn.microsoft.com/.../ms714639(v=vs.85).aspx

John Nichols

Sage
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 mcolwell over 9 years ago in reply to jcnichols

Actually, it looks like they were originally set up as Strings, rather than date types. :(
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 jcnichols over 9 years ago in reply to mcolwell

what the data format look like, can you paste an example?

Thanks john
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 mcolwell over 9 years ago in reply to jcnichols

The data is entered in time format (24 hour time). So it would be something like 13:00 or 17:45 or 08:15
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 jcnichols over 9 years ago in reply to mcolwell

Hi,

This will convert your string UDF to a SQL Time and then return the difference in minutes.

{fn TIMESTAMPDIFF(SQL_TSI_MINUTE, {fn (SO_SalesOrderHistoryHeader.UDF_TIMEIN, SQL_TIME)},

{fn CONVERT(SO_SalesOrderHistoryHeader.UDF_TIMEOUT,SQL_TIME)})}

Thanks

John Nichols

Sage
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Sage 100

Time Difference In Query

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING