Helen,
Thanks for your responce, but I can see I did not make myself clear.
It is not the fields of type date that are giving me trouble, but
the fields of type time.
For example, I have a field 'timeofpickup' with value '530 PM' and a
field 'timeofdropoff' with a value of 1:30 AM.
(I realize that internally these are stored as integers representing
milliseconds since midnight).

I can, of course do the following:
select
timeofpickup, timeofdropoff,
(timeofdropoff - timeofpickup)...

but that gives me the unuseful value of -57600
<grin>.

How do I get the elapsed hours between timeofpickup and
timeofdropoff when the former is before midnight and the latter
after midnight(next day)? Of course, the time field itself knows of
no such thing as "next day."

Also, I do want to be able to do this in a stored procedure because
I want to use the resulting value as part of a calculation
determining total cost (this is a transportation app). Now, I may
not go that route (excuse the pun) and may just continue doing these
calculations in OnCalcFields or some such in Delphi. Still, I'd like
to *know* how to do it in a SP.

Steve


Steve
At 08:45 AM 8/29/2003 +1000, you wrote:

At 07:49 PM 28/08/2003 +0000, you wrote:

>This is a question about calculating elapsed time using two fields
>of type time, when the later once is past midnight.
>
>I've recently converted tables from Paradox, where I used datetime
>data types. In the firebird tables, I used date and time datatypes,
>thinking there might be some advantage.
>
>I want to write a stored procedure that calculates the elapsed time.
>I have what amounts to start_time and end_time fields. It seems that
>I need to subtract the milliseconds of the start_time field from the
>milliseconds in a day, and then add to the result the milliseconds
>of the end time, then convert to hours (floating point).
>
>But I don't know how to do that. I've tried variations on cast() but
>can't get them to work.

No, you don't need a SP or any of that decoding stuff. Simply
subtract the
earlier date from the later date to get a double precision number
which is
time elapsed in days. It is just an ordinary expression, so you can
select
it as a computed output column, fetch it into a variable in a SP,
use it to
update a column, etc. Works with DATE, TIME or TIMESTAMP types.

heLen