Re: Still more SQL date weirdness

On 01-Jun-2011 11:41 , James Lampert wrote:

DUHHHHHH!!!!!

I had a value of 00000001 in one of the zoned-date fields of the
original PF.

Obviously, I need to handle that case.

Yesterday and this morning, Chuck, Rob, et al. said something
about a "handler." Not quite sure how that would work.
Hmm. I see that "SQL for Dummies" has something on it.

I'll let you know if I need more help with this.

CRPence wrote earlier:

Perhaps adding after the BEGIN:
declare continue handler for SQLEXCEPTION return NULL;

So a revision to the original function, given Rob's assumption about an unstated desire to avoid the database NULL value, then add the HANDLER after the "begin" for the existing routine source; then DROP FUNCTION and CREATE FUNCTION anew with that modified source:

create function FOO/DISPLAYDAT(i_date NUMERIC(8))
returns DATE
language sql deterministic not fenced set option datfmt=*ISO
begin
declare continue handler for SQLEXCEPTION return date('00010101000000');
if i_date = 0 then return date('00010101000000'); end if;
return date(digits(i_date) concat '000000');
end

Because the UDF is so "slim", handling any SQL exceptions in that manner may be acceptable, as compared with only handling the specific SQLSTATE value [or values] for the possible exceptions in date handling. I think the SQLSTATE of '01534' should cover all invalid date syntax possibilities in that UDF, although I alluded in my prior post about the SQLSTATE of '22007' being another possible effect. But because I would not want to waste my time figuring out specifically which SQLSTATEs to handle, that is why I mentioned just handling SQLEXCEPTION. I also would want to use the NULL value [or a different value than the zero-date representation as Rob also alluded] in order to ensure knowing the difference between the bad data case and the apparently acceptable zero-date case.

Regards, Chuck