Subject Re: [ib-support] Is this a firebird bug ?
Author Helen Borrie
At 05:27 PM 13/03/2003 -0300, you wrote:
>I'm getting :
>Incompatible column/host variable data type.
>Dynamic SQL Error.
>SQL error code = -303.
>conversion error from string "30-MAY-2000".
>inside a stored procedure that is executing the following command :
>select first 1 p.peso,p.altura
>from peso p
>where (p.brinco = :Brinco) and (( - :dNasc) <= 20)
>order by p.brinco,
>into :Peso0, :Altura0;
>30-may-2000 is the content of dNasc variable that is defined as DATE.
> is a field defined as DATE too.
>Any ideas ?
>PS: Anyway, it's very interesting the error message. Why FB is trying
>to convert the date from a string when the variable is already defined as
>DATE type ?

In ODS10, you need to CAST all of your date literals.

in the SP (and not certain that, by now, it is not too late):

and (( - CAST(:dNasc as DATE) <= 20)

If this is is a Dialect 3 database and the CAST fails, then it IS too late
and I would agree there is a bug.

OTOH, if this a Dialect 1 database, then DATE is a timestamp - Dialect 1
doesn't support a date-only type - your date literal *isn't* valid for a
Dialect 1 date type. It would need to be '30-MAY-2000 00:00:00' and you
would still need to CAST it for expression evaluation. You would also have
a bit more work to do on your date evaluation to ensure you correctly catch
the dates evaluated by your expression...