Re: Another SQL newbie question

[James Lampert <jamesl@xxxxxxxxxxxxxxxxx>]

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