Calculating hours in SQL


I am looking to calculate the amount of time (Hours) spent on a order. Im am using VB.NET with a Mircosoft Access 2003. Table looks like this:

BookingID   OrderID    MachineID   OperatorID Status         DateLog                TimeLog
94             32            1                12              SIGN ON       18/04/2006      17:00
95             32            1                12              SIGN OFF      18/04/2006       21:00
96             32            1                6                SIGN ON       20/04/2006      11:00
97             32            1                6                SIGN OFF      20/04/2006       15:00

There has been 8 hours spent on OrderID 32 so far. What you be the best way to get this total in an SQL statement or series of SQL statements?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SELECT datediff("h",[DateLog],[TimeLog]) as timediff
FROM Table1;

Your error is in the timelog column that you need a date and in datelog you need a time, use Now() to assign this to the column

Good Luck

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
daly__paulAuthor Commented:
Yes, took your advice on the Now() which will generate the datestamp.

Changed the database design to accommadate the Sign On and Sign Off on the same booking ID and then set the query:

SELECT SUM(DateDiff( 's', SignInLog, SignOutLog )) AS Diff FROM Booking WHERE OrderID = " & TXT_OrderNo.Text & "

Working great now.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.