Re: Difference between two dates and times

From: John Vinson (jvinson_at_STOP_SPAM.WysardOfInfo.com)
Date: 10/20/04 

Date: Wed, 20 Oct 2004 11:55:47 -0600

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: Access, MyODBC, MySQL Questions .....
    ... store the current date/time in the field. ... This is different than the SQL Server Timestamp field which automatically stores a binery representation of the current date/time that is not user usable. ...
    (comp.databases.ms-access)
  • RE: Calculating next due date in a form
    ... Date/Time data types are a point in time, not a duration. ... is a calculation between two times and is not represented as point it time. ... For each installation there is an install table which links to job_id ...
    (microsoft.public.access.forms)
  • Re: Calculated Field for Week Ending
    ... Then using that calculation to get Friday as the last day of the week would ... with a date/time field in Access (for example, ... date) is fairly easy (there are many date/time functions available). ... Microsoft Office/Access MVP ...
    (microsoft.public.access.queries)
  • Using a Cube to store Histogram information?
    ... I am running regressions off of SQL server using VBA for calculation. ... I would like to be able to create histograms with ... Since there are lots of variables in the regression, ...
    (microsoft.public.excel.programming)
  • Re: Using Recursion looping through a ADO RS
    ... % is the modulo division operator in SQL 2000. ... itemId = 20" to SQL Server Query Analyzer, ... >> calculation. ... I open the recordset, exctract the RawData and after doing some ...
    (microsoft.public.dotnet.languages.vb)