Calculating hours in SQL

Hi,

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?

daly__paul
daly__paulAsked:
Who is Participating?
 
gangwischCommented:
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
0
 
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.

Daly__paul
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.