Re: Still more SQL date weirdness (Trying again)

It sounds like your job is set with a default date format in one of the 2
digit year formats. Those have a restricted range of valid values. 1940 to
2039 I think. anything outside of that year range would error or display
as invalid or ++++++++++ from IBM's restrictions.



From:
James Lampert <jamesl@xxxxxxxxxxxxxxxxx>
To:
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date:
05/31/2011 04:16 PM
Subject:
Still more SQL date weirdness (Trying again)
Sent by:
midrange-l-bounces@xxxxxxxxxxxx



The first time I tried to start this thread, the note in the subject
header, about my vacation having gone well, caused it to be suppressed.

So you may very well have read the question already (after David
manually released it), without any answer managing to post successfully.

I learned of this while still on vacation.

The thorny little problem of converting PF numeric dates into actual
date fields in a VIEW on the PF is still giving us trouble.

Given that the PFs involved have their date fields defined as 8-digit
zoned decimal, in the format YYYYMMDD, and that the SQL VIEW definition
massages them with:

CREATE VIEW FOO/BAR (CDATE, . . . ) AS SELECT DISPLAYDAT(CDATE) . . .
FROM FOO/BAZ;

and DISPLAYDAT(i_date) is defined as:

create function FOO/DISPLAYDAT(i_date NUMERIC(8))
returns DATE
language sql deterministic not fenced set option datfmt=*ISO
begin
if i_date = 0 then return date('00010101000000'); end if;
return date(digits(i_date) concat '000000');
END

if I look at the VIEW in QuestView, the converted date field shows up as
a "Type-L" date field, with the correct value, and if I look at it in
SQUIRREL SQL, just fetching the records without manually entering any
SQL, I get the same.

BUT

If I do

SELECT * FROM FOO/BAR

in a STRSQL session, I get 2-digit years, with "++++++++" for any dates
that can't be displayed as 2-digit years, and if I plug the same into
SQUIRREL, I get nothing at all.

If I do
SELECT *
FROM FOO/BAR
WHERE ACCOUNT_ID = '100501'

in STRSQL, I get the "++++++++" again, while in SQUIRREL, I get the
expected ISO date, with "Warning: [SQL0181] Value in date, time, or
timestamp string not valid."

And if I do
SELECT *
FROM FOO/BAR
WHERE CDATE>= DATE('2011-01-01') AND CDATE < DATE('2011-03-01')

STRSQL gives me "Query cannot be run. See lower level messages," with
"Syntax of date, time, or timestamp value not valid." apparently being
thrown *within* DISPLAYDAT, while SQUIRREL and BIRT (the latter being
the whole raison d'etre for this silly-go-round) produce similar errors.

However, if I do the same thing with 2-digit years in the WHERE clause
(at least in STRSQL and SQUIRREL), it seems to work.

Can anybody shed any light on this?

--
JHHL