I am trying to calculate worked hours for payment just like Excel does when a cell is formated as [HH:mm:ss].

Thanks.

Roberto Carlos




Em 18:50, Helen Borrie   escreveu:

At 01:49 AM 11/11/2008, you wrote:

>How can I calculate an interval of time?

An "interval" is the difference between a time or date/timestamp value and another time or date/timestamp value:

a) subtract the smaller TIME value from the larger TIME value to get the interval in seconds. The result is a DECIMAL(9,4).

b) subtract the smaller DATE or TIMESTAMP value from the larger DATE or TIMESTAMP value to get the interval in days. For DATE types the result is an integer. For TIMESTAMP, it is DECIMAL(18,9).

You can add a number to a date or timestamp to advance a date or timestamp. You can concatenate a TIME value to a DATE value and cast the result as a TIMESTAMP. **Do this with care**. TIME rolls over to 00:00:00.000 every 24 hours!!

>For example:
>
>1. '16:40:35' + '15:25:37' = Â '32:16:12' (32 hours 16 minutes 37 seconds)

Not valid. You can't add date or time values together; and there is no valid TIME value higher than 23:59:59.9999. (What would you be trying to calculate here, anyway????)

>2. '166:40:50' - '6:20:30' = '160:20:20' (160 hours 20 minutes 20 seconds)

Not valid. '23:59:59' - '6:20:30' would give a valid result: it would be a number in seconds and thousandths of seconds (actually milliseconds under the default settings in Fb 2 and above).

There is no SQL function that directly outputs intervals of time in hh:nn:ss format, which would be a string, not a date/time literal. You will have to write expressions to construct such strings. It is possible that someone has written a UDF to calculate such strings...you could ask on firebird-tools...

./heLen


[Non-text portions of this message have been removed]