Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Posts
    13

    Unanswered: Elapsed Time from Data Server

    Well, I've read about all I can read in this forum, so I pose yet another elapsed time question:

    I'm working with data that shows time as a number. Example 23:59:59 is displayed as 235959. I cannot change the format the the time data is, so everytime I run the query (make table query), it is stored as a 'number' format. When I change the input mask, it works for the current records, but when I run the query again, it's back to it's original format. Secondly, I need to calculate the elapsed time between a 'start time' and 'end time'.
    I think I can get to the elapsed time part, if I could only get the initial data to stay in the correct format. Is there something I'm missing? All I need to do is convert the start time and end time into 00:00:00 format and keep it that way.

    Thanks in advance. This is my last hope before I hang myself from a tree. I've been beating this thing around for about a week or so. Can't seem to get it.

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    I hope that I understand the problem correctly...

    This is what I would do. In the make table query, format the time data the way that you want. You might need to use the cDate() function as well to make the field be date/time in the new table.

    Something like this: SELECT CDate(Format([time_data],"00\:00\:00")) AS Expr1 INTO new_table FROM original_data

    Does that make sense? Convert that data before you put it into the new table.

    C

  3. #3
    Join Date
    Sep 2006
    Posts
    13
    I think you understand. I just don't understand where you place that function statement. The time data is currently in a number format 235959. I need to be able to format the data as 23:59:59, in two different fields (start time and end time), then calculate the difference for a final elapsed time. I was hoping to be able to do this with one query, but if not, I would be happy with just getting the data into the table with the proper format.

    Is the statement above an expression in the query, or should I use it as criteria for the current time data fields already within the query?

  4. #4
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    OK, I think I have it now.

    This is the SQL query itself: SELECT Format([start_time],"00\:00\:00") AS s_time, Format([end_time],"00\:00\:00") AS e_time, DateDiff("s",[s_time], [e_time]) AS time_difference FROM your_table

    The "Format([start_time],"00\:00\:00") AS s_time" just converts your number into the time format that you want and then aliases the field as s_time. I then do the same for the end time. Next I use the DateDiff function to calculate the number of seconds between the two times. You might have to do some additional formatting depending how you want the result displayed.

    C

  5. #5
    Join Date
    Sep 2006
    Posts
    13
    Got it. Thanks for the help. I had to break it down into two steps, but it works and I'm happy. I really didn't want to hang myself, so I owe you my life.

    Thanks again. You guys always pull through.

Posting Permissions

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