MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » February 2013

Issue with dates in SQL -- errors under some conditions, but not others.



fixed

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))
returns DATE
language sql deterministic not fenced set option datfmt=*ISO
begin
declare continue handler for sqlexception
return date('99991231000000');
if i_date = 0 then return date('00010101000000'); end if;
return date(digits(i_date) concat '000000');
END

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."

--
JHHL





Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM 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 here. If you have questions about this, please contact