Difference between two dates and times

[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.

 

[Michel Walsh]

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

 

[John Vinson]

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".

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.

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

 

[Guest]

This is indeed exactly what I needed to know. Thanks much.

 

[Guest]

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.

 

[Jamie Collins]

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!

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.

--