data less than 30 minutes

gudii9
gudii9 used Ask the Experts™
on
i am trying below kind of query which should give count less than 30 minutes of difference of time. not able to execute below. How to modify to make it work

select count(order_nbr) from order INNER JOIN timings ON order.order_id=timings.order_id WHERE LEN(order_nbr) = 11 AND order_cd=02  AND order.time_ins- timings.time_upd <30 minutes
please advise
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
AND order.time_ins- timings.time_upd > DATEADD(MINUTE,-30, GETDATE() )

Author

Commented:
Msg 8117, Level 16, State 1, Line 1
Operand data type datetime2 is invalid for subtract operator.
Database Consultant
Top Expert 2009
Commented:
sorry i missed the '-'

  select count(order_nbr) from [order] INNER JOIN timings ON [order].order_id=timings.order_id WHERE LEN(order_nbr) = 11 AND order_cd=02  

  AND DATEDIFF(MINUTE,  [order].time_ins,timings.time_upd) >30
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
select count(order_nbr) from order INNER JOIN timings ON order.order_id=timings.order_id WHERE LEN(order_nbr) = 11 AND order_cd=02  AND order.time_ins- timings.time_upd > DATEADD(MINUTE,-30, GETDATE() )

Open in new window

i used like above
AneeshDatabase Consultant
Top Expert 2009

Commented:
use the sql statement on my previous comment  or

 AND order.time_ins  > DATEADD(MINUTE,-30, timings.time_upd  )

Author

Commented:
Msg 174, Level 15, State 1, Line 2
The datediff function requires 3 argument(s).
AneeshDatabase Consultant
Top Expert 2009

Commented:
can you paste the exact query you are trying,  there are 3 arguments already in the datediff statement ,

datediff (MINUTE,  [order].time_ins,     timings.time_upd   )

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial