Re: Does environment variable QIBM_QBASEYEAR affect timestamp variables? -- MIDRANGE-L

Peter,
Item 1 of 2: What you say seems to still be true, in certain areas. For
example, in STRSQL values timestamp(date('04/05/39')) returns 2039 as the
year and values timestamp(date('04/05/40')) returns 1940 as the year.
Item 2 of 2: You're also right about timestamps. It shouldn't matter.
That belief and the fact they are using 69/70 instead of 39/40 makes me
believe that they have some really weird stuff going on in that table
function. Hopefully the case I've opened gets to the root of it.

On Wed, Apr 5, 2023 at 1:33 PM Peter Dow <petercdow@xxxxxxxxx> wrote:

What I find odd is that

https://www.ibm.com/support/pages/node/6579221?mhsrc=ibmsearch_a&mhq=QIBM%26lowbar%3BQBASEYEAR

says

"The current supported date range for t2-digit year date formats is
January 1, 1940 – December 31, 2039. When using a date format that only
includes the last two digits of a year, the system assumes years 40 to
99 are 1940 - 1999, and years 00 to 39 are 2000 - 2039."

But timestamps have a 4-digit year, so why does this apply to them?

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx
tpdow@xxxxxxxxxxxxxx

/
On 4/5/2023 5:07 AM, Rob Berendt wrote:

Environment: IBM i 7.5, TR 1, cume 22321, Hiper 22, db 3.

While looking at a different thread in which a gentleman tried to use all
inclusive timestamps to get beginning to end I noticed something odd.
He was using a sql service with parameters like
starting_timestamp => '0001-01-01-00.00.00.000000',
ending_timestamp => '9999-12-31-23.59.59.999999'
and it wasn't finding anything.
I suggested he just follow the doc for that function, leave those blank

and

it would work. However I did delve into this further.
The starting timestamp was fine.
It was the ending timestamp.
If I used '3069-12-31-23.59.59.999999' it worked fine.
If I used '3070-12-31-23.59.59.999999' I would get
SQL State: 42616, Vendor Code: -443, Message: [SQL0443] ENDING_TIMESTAMP

IS

EARLIER THAN STARTING TIMESTAMP

https://www.ibm.com/support/pages/node/6579221?mhsrc=ibmsearch_a&mhq=QIBM%26lowbar%3BQBASEYEAR

Will someone on an older OS which still supports this function try 2039
then try 2040?
select *
from table (qsys2.display_journal ('QSYS',
'QAUDJRN',
starting_receiver_name => 'Q633900051',
starting_receiver_library => 'QSYS',
ending_receiver_name => 'Q633900051',
ending_receiver_library => 'QSYS',
starting_timestamp => '0001-01-01-00.00.00.000000',
ending_timestamp => '3070-12-31-23.59.59.999999'
)) ;

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.