Difference between two dates and times

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two columns in a table. One is START_TIME the other is END_TIME and
they are both TIMESTAMP data types formatted as "MM-DD-YYYY HH:MM:SS AM/PM".

In a third column (ELAPSED_TIME) I want to calculate the difference between
the first two columns (i.e. END_TIME - START_TIME) and show the difference
with a picture of: DD HH:MM:SS or DD-HH:MM:SS (example result: 00 00:01:34 or
00-00:02:18).

My calculation is running in the current Access DB project and will update
the table with the calculation using SQL. PLEASE NOTE: Sometimes this process
runs across two days, e.g. may start at 11:40 pm and may finish at 12:10 am.

Thanks much in advance for your assistance.
 
Hi,


Int( EndingTime - StartTime) & " " & Format( EndingTime-StartTime,
"hh:nn:ss")



since date_time values are internally stored in days and portion of days
(0.5 = half a day = 12 hours)., as long as the result is positive. Imposing
a hh:nn:ss format supply up to 24 hours. Taking the integer part of the
difference supply the number of day, but display a 0 if there is less that
24 hours. I assume the time get their associated DAY with them, ie, it is
not 11:40PM but 10-20-2004 11:40PM.



Hoping it may help,
Vanderghast, Access MVP
 
I have two columns in a table. One is START_TIME the other is END_TIME and
they are both TIMESTAMP data types formatted as "MM-DD-YYYY HH:MM:SS AM/PM".
Click to expand...

Are these SQL Server tables? Access (JET) tables have a Date/Time
datatype but do not have a Timestamp.
In a third column (ELAPSED_TIME) I want to calculate the difference between
the first two columns (i.e. END_TIME - START_TIME) and show the difference
with a picture of: DD HH:MM:SS or DD-HH:MM:SS (example result: 00 00:01:34 or
00-00:02:18).

My calculation is running in the current Access DB project and will update
the table with the calculation using SQL. PLEASE NOTE: Sometimes this process
runs across two days, e.g. may start at 11:40 pm and may finish at 12:10 am.
Click to expand...

Take a look at the DateDiff() function in the VBA help. I believe it
will work with TIMESTAMP values as well as Date/Time values but I'm
not absolutely certain! It will give you a difference in your choice
of units (from seconds to years) as an integer.

I would strongly recommend that you NOT store the ELAPSED_TIME in any
table field; it's redundant and can be calculated whenever it's
needed.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Thanks for your response, but I'm only using this to track the running time
on queries. This information is not recorded anywhere else.

Also, I am not using Access in a conventional manner, only as a container
for large amounts of data. I summarize the data and port it into Excel to do
my number crunching. My tables only contain the results of ADO queries run
against an Oracle DB.
 
Are these SQL Server tables? Access (JET) tables have a Date/Time
datatype but do not have a Timestamp.

Take a look at the DateDiff() function in the VBA help. I believe it
will work with TIMESTAMP values as well as Date/Time values but I'm
not absolutely certain!
Click to expand...

Does Jet have a TIMESTAMP data type? Yes and no <g>:

CREATE TABLE QuartzClocks (
MyKeyCol INTEGER NOT NULL,
start_date TIMESTAMP DEFAULT NOW() NOT NULL,
end_date TIMESTAMP NULL
);

However, TIMESTAMP merely maps to Jet's DATETIME data type.

Jamie.

--
 
Back
Top