Re: Another SQL newbie question

[CRPence <CRPbottle@xxxxxxxxx>]

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