Subtracting two datetime values keeps failing.
Posted on 2005-04-08
I'm merely trying to determine the time it took to receive an order acknowledgement. So, you get the ordertime of an order that comes in, then subtract that time from the ordertime of the subsequent acknowledgement -- but I keep erroring out with Server: Msg 512, Level 16, State 1, Line 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
my code is below. i've changed several times. right now i'm just trying to get the calculation going. understand, all orders are in the same table. each order has an ordernumber. and the acknowledgement for that order can be associated with it via it's originalordernumber.
I'm nearly there, I'm just hoping somebody can help me get around this msg 512. Please advise.
declare @odate datetime
declare @adate datetime
SET @odate = (select orderdate from orders where msgtype = 'o' and orderdate >= left(getdate()-0, 11) and endpoint = 'aaa')
SET @adate = (select orderdate from orders where msgtype = 'a' and orderdate >= left(getdate()-0, 11) and endpoint = 'aaa')
SELECT CAST(DATEDIFF(SECOND, @odate, @adate) / 86400 AS VARCHAR(2)) + ' Ack Time, '
+ CONVERT(CHAR(8), DATEADD(SECOND, DATEDIFF(SECOND, @odate, @adate) % 86400, '1900-01-01'), 8)