[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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
0
daly__paul
Asked:
daly__paul
1 Solution
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now