none
calculate times in a table RRS feed

  • Question

  • Hi All

    I have a database with a lot of times. Example Times a truck arrived, Time loading started and ended.

    I need to calculate the time between the start loading and end loading. If the time is on the same date, there is no issue, but if the time roll from Monday 23:00 to Tuesday 02:30, I get a lot of garbage.

    Is there a way I can calculate the times correctly? I specifically need this to be performed using a SQL statement and not a query, but a query will be most helpful as well.

    Thanks

    Deon

    Tuesday, May 7, 2019 2:02 PM

Answers

  • An SQL statement and a query is the same. 

    You can use an old trick in this expression in the SQL:

    TimeValue(CDate(1 + [EndTime - [StartTime])) As LoadTime

    The output will be a datetime value you can format as you like.


    Gustav Brock

    • Marked as answer by Deon SA Friday, May 10, 2019 10:10 AM
    Tuesday, May 7, 2019 2:49 PM
  • You need to store the entire datetime, not just the time. If you store the entire thing, time happening over midnight will be easy to compute. A simple DateDiff("interval as string", [StartTime], [EndTime])

    Use "n" for minutes; "h" for hours.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by Deon SA Friday, May 10, 2019 10:10 AM
    Tuesday, May 7, 2019 4:05 PM

All replies

  • An SQL statement and a query is the same. 

    You can use an old trick in this expression in the SQL:

    TimeValue(CDate(1 + [EndTime - [StartTime])) As LoadTime

    The output will be a datetime value you can format as you like.


    Gustav Brock

    • Marked as answer by Deon SA Friday, May 10, 2019 10:10 AM
    Tuesday, May 7, 2019 2:49 PM
  • You need to store the entire datetime, not just the time. If you store the entire thing, time happening over midnight will be easy to compute. A simple DateDiff("interval as string", [StartTime], [EndTime])

    Use "n" for minutes; "h" for hours.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by Deon SA Friday, May 10, 2019 10:10 AM
    Tuesday, May 7, 2019 4:05 PM