We have a UDF used by a number of SQL VIEWs, to convert a numeric date
field to an actual SQL date, defined thusly:
create function WTJASFWD/DISPLAYDAT(i_date NUMERIC(8))
language sql deterministic not fenced set option datfmt=*ISO
declare continue handler for sqlexception
if i_date = 0 then return date('00010101000000'); end if;
return date(digits(i_date) concat '000000');
If we look at the VIEWs that use this in QuestView (i.e., via native
record-level access), any zeroed-out fields show up as January 1st of
the year 0001, as designed.
But if we look at it in STRSQL, those same zeroed-out dates come back as
a field of plus-signs.
And when it's used under other circumstances, apparently in connection
with BIRT reports, we get exceptions in the QZDASOINIT jobs.
Can somebody please shed some light here? Why would the behavior differ,
depending on the circumstances?
The whole point is to have zeroed-out dates show up as an origin date,
and bad dates show up as something as close as we can get to "heat-death
of the universe."