At 03:02 AM 18/04/2005 +0700, you wrote:

>Dear all,
>
>I found out the hard way tonight that the following stored procedure
>code doesn't work... Firebird 1.5.2, Win32, Dialect 3 database.
>
>...
>declare variable begin_time time;
>declare variable end_time time;
>declare variable delta time;
>...
> //calculate delta after fetching begin_time and end_time
> delta = end_time - begin_time;
>...
>
>The calculation seem to fail everytime with the following error message:
>"Overflow occured during data type conversion. Conversion error from
>string "3000.0000"."
>
>After some fiddling, it seems that the result of the (end_time -
>begin_time) expression is processed as a numeric data.

Your conclusion is wrong. The result of subtracting one TIME var from
another is numeric(9,4). That is, it is an *interval* of seconds and
thousandths of seconds.

Your variable delta needs to be a numeric(9,4).

>Changing the
>delta variable into an integer solves the issue, partially...
>
>...
>declare variable delta integer;
>...
> delta = end_time - begin_time;
>...
>
>But now the delta variable holds an INTEGER containing elapsed time in
>SECONDS, while I need this to be a TIME or TIMESTAMP format...

Sorry, but there is no logic to this. TIME is not an interval, it is a
time on the 24-hour clock. What are you going to do with this TIME
value? Add it to midnight? In that case, as Ali suggested, add the
calculated interval (delta as a numeric(9,4) to CAST('00:00:00.0000' AS TIME).

>Manually
>casting it back to TIME data fails with the same error message as above.
>Am i missing something here?

Yup. Linguistic confusion. TIME answers the question "what is the time?",
not "what time did it take?"

>The funny thing is that I recall writing some similar code in the past,
>but i don't remember having *any* problems like this.

Nothing has changed regarding date arithmetic.

./heLen