Results 1 to 4 of 4

Thread: subtract time

  1. #1
    Join Date
    May 2002
    Location
    at the corner of flotsam & jetsam, just within the walls of reality
    Posts
    21

    Unanswered: subtract time

    hello:

    i have a table with two date/time fields labeled BegTime and EndTime for Beginning Time and Ending Time, respectively.

    i would like to create a query where i can subtract time between these two fields (so that, in another query, i can take sum, avg, etc.).

    e.g., in a record where:
    BegTime = 1730
    EndTime = 1800

    in the query, the result would be 30.

    when i try to do this, i always get .00 or .0123xxxx (something like that). i set this "totalTime" field = [EndTime]-[BegTime], but i'm still getting these funky decimal numbers.

    any thoughts? thanks in advance.

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    DateDiff

    Try Duration=datediff("n",[EndTime], [BegTime])

    Not at my PC so I cant test though!

    Else look up datediff on microsoft knowledgebase

    http://msdn.microsoft.com
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Mar 2003
    Posts
    8

    another

    try this
    SELECT time.id, Hour(([endtime]-[begtime])) AS [Hour], Minute(([endtime]-[begtime])) AS [Minute]
    FROM [time];

    wish you well

  4. #4
    Join Date
    May 2002
    Location
    at the corner of flotsam & jetsam, just within the walls of reality
    Posts
    21
    the datediff worked! thank you sooo much!

    (i also found the datediff explanation here on the msdn site.

    thanks again, all, for the feedback!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •