Re: SQL error - syntax of date value not valid

One common technique to improve performance, and to avoid the error you're
getting, is to change
date(inEffDate) = a.effDate
to
inEffDate = cast(a.effDate) as char 8 (or some such syntax)

It may even be better to just do this with a variable before the
comparison as in

declare charEffDate char(8) ;


DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
begin
...
set charEffDate = cast(a.effDate) as char 8
...
end ;

select COUNT(*)
INTO VCOUNT
from prugadm
a
where inEffDate = ' ' or inEffDate = charEffDate ;

You can pretty that up to whatever date format you're using.
I don't want to take up the time to look up the exact syntax but you'll
get the idea.

Why bother putting each row through conversion?

Rob Berendt