Re: Another SQL newbie question

On 12-May-2011 16:52 , James Lampert wrote:

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.

The 100-year window for two-digit years is 1940-01-01 to 2039-12-31, so the year 1931 is not functional for character representation when the DatFmt option defaults to any of the supported *JOB values which are all two-digit year formats. If whatever "blows up" is defined to default date representations to a two-digit year, then the effect is presumably correct for a date underflow.

Always using the ISO format [or another 4-digit year format] enables the earliest date value of 0001-01-01 all the way up to 9999-12-31 to be functional. So even if someone might be using a date format like *MDY, the value '0001-01-01' can still be INSERT INTO a date column, even though in the same program there would be difficulties accessing that data in character form due to date underflow. The UDF effectively uses only ISO [per non-delimited timestamp] so it has no problem, but if the UDF might have needed to use some date strings, then best to also, just before the "begin", add the line:
SET OPTION DATFMT=*ISO

Note that the CHAR scalar\cast function allows a second parameter to map a date to character in the ISO form [for example] similarly to allow the smaller or larger dates to avoid the date underflow and date overflow conditions. So in the interface used to test the UDF, instead of finding the session attribute to set date formats to ISO, use instead the following UDF invocation:
CHAR( DISPLAYDAT( xxx ) , ISO )
instead of:
DISPLAYDAT( xxx )

Sometimes my preference would be to return CAST(NULL AS DATE) for an input of zero, or in response to an error for any bad inputs like 20010229 or 99999999. In that case adding a HANDLER to the SQL function to directly RETURN that, or return the DATE variable when that can be set without an error.

Regards, Chuck