Re: Another SQL newbie question

Prodded to do so by the fellow at the next desk, I've finally gotten back to the date conversion problem, and I ended up with this UDF:

> create function WTDEVELOP/DISPLAYDAT(i_date NUMERIC(8))
> returns DATE
> language sql deterministic not fenced
> begin
> if i_date = 0 then return date('00010101000000'); end if;
> return date(digits(i_date) concat '000000');
> END

It works, so long as it's fed a valid YYYYMMDD value as a parameter. But when it's fed zero, and trips the if-statement, it blows up.

So I tried different default date values. Like 19000101 I finally got it to work for zero values by using my birthdate as the default.

With further experimentation, I find that 19410101 works, but 19310101 blows up.

Is my memory playing tricks on me? I thought the floor value for the DATE (L in DDS) data type was 00010101, and I see that "SQL for Dummies" gives that as the floor for an SQL DATE.

--
JHHL