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');

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.


This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].