Calculating hours in SQL

Posted on 2006-04-20
Last Modified: 2010-04-23

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?

Question by:daly__paul
    LVL 10

    Accepted Solution

    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

    Author Comment

    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.


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Suggested Solutions

    This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
    Introduction When many people think of the WebBrowser ( control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now