Re: Difference between two dates and times

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: quartz (quartz_at_discussions.microsoft.com)
Date: 10/20/04 

Date: Wed, 20 Oct 2004 11:15:08 -0700

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.

"John Vinson" wrote:

> On Wed, 20 Oct 2004 07:19:04 -0700, quartz
> <quartz@discussions.microsoft.com> wrote:
>
> >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
>

Relevant Pages

  • RE: Form Calculations
    ... Queries do not store any data. ... you can do the calculation in the report itself. ... Copy the code I posted into a standard module. ...
    (microsoft.public.access.forms)
  • Re: Table cell display differences between 97 and XP
    ... To Graham Mayor ... Access that are either + or - calc don't have display ... >> fields from the access queries to calculate VAT will not ... >> If you ROUND the calculation in the query the resulting ...
    (microsoft.public.word.mailmerge.fields)
  • Re: anybody use MySQL?
    ... You, however, do have to download and distribute the ODBC driver to every ... time and timestamp consistently. ... Also, if queries, especially one written for recordsources or rowsources, ... row if it does not have a primary key. ...
    (microsoft.public.access.gettingstarted)
  • RE: "Rewrite" an application without affecting old calculations
    ... I am assuming that you are using queries to populate forms and if this is ... you are recording the date of the invoice. ... the date you switched to the new calculation method and if so, ... to set the new query as the record source for the form other wise, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Subtracting Microseconds from Timestamp
    ... This timestamp form doesn't contain ... accordingly in microseconds or nanoseconds. ... dn> query is 80192300. ... If you need to get interval between two queries, ...
    (comp.unix.programmer)