Time Difference

[jlclark4]

Hey,



I am attempting to run a query that will give me time difference between ProcessEndB and ProcessStartB. All are in a Date/Time format. I have tried googling several different sites but most give information on subtracting Dates and not Time. Any help is greatly appreciated. I have attached a sample database below.


Thanks!

[Robeen]

Are "ProcessEndB and ProcessStartB" fields in a Table?

Are the values contained in them strictly Time values - or do they contain Date AND Time?

[jlclark4]

Hey Robeen,

ProcessEndB and ProcessStartB are fields in the table (see Example), they are also strictly time.

[Robeen]

I have never had to calculate the Time Difference between fields before.
Using the DateDiff function allows you to get either hours, Minutes or Seconds.

Try this SQL in your query.
In design view, Click 'View', 'SQL View', and then paste this in:

 
SELECT tblCities.EntryNumber, tblCities.City, tblCities.ProcessStartA, tblCities.ProcessStartB, tblCities.ProcessEndB, DateDiff("h",[ProcessStartB],[ProcessEndB]) AS TimeElapsedHours, DateDiff("n",[ProcessStartB],[ProcessEndB]) AS TimeElapsedMinutes, DateDiff("s",[ProcessStartB],[ProcessEndB]) AS TimeElapsedSeconds
FROM tblCities;

This will show you that you can get the time difference in three different formats.

If you want to see something like this for the elapsed time:
04:17:53
You might consider creating a Function and sending it your start & end times and then doing the Math in the Function.

You would 'call' the function from your query and pass it the start and end times.

Let me know if you need help.

[rpeare]

What do you want your output to look like. If there's a possibility that the time will go into days/weeks/months you there is no standard format for that.

If your times will always be in a number of hours, minutes and seconds you'd have to build an expression. Secondly there's no real reason to store your date/time as two separate fields, it's just going to make things more complicated for your calculation.

I change your table to have two new fields

PROCESSSTART
PROCESSEND

each is a date/time field and instead of keeping the day and time separate I joined the values you have in there into a single field then I used this SQL statement to build a query. I left everything in separate fields so you could see how it went together:

SELECT tblCities.EntryNumber, tblCities.City, tblCities.ProcessStart, tblCities.ProcessEnd, DateDiff("s",[processstart],[processend]) AS SecDiff, Int([secdiff]/86400) AS Days, Int([secdiff]/3600)-([days]*24) AS Hours, Int([secdiff]/60)-([hours]*60)-([days]*1440) AS Minutes, [secdiff]-([minutes]*60)-([hours]*3600)-([days]*86400) AS Seconds
FROM tblCities;