This is not an SQL problem but an RPG problem.
RPG adds for each host variable an additional Variable (SQL + 5 digit
counter). When defining date fields it takes the date format of the compile
option DATFMT (which is per default *JOB with a 2-digit year).
While SQL (itself) always uses the scaliger no (of a date) and only uses the
date format for displaying the data in a readable form, converts RPG the
date into a character representation with the date format of the variable.
RPG does not convert the character data into the scaliger no before
rewriting it back into the database.
To avoid any problems, change the DATFMT in the compile command (CRTSQLRPGI)
to any format with a 4 digit year or even better add an SET OPTION Statement
with the date format set to a format with a 4 digit year.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
Database and Software Architect
IBM Champion since 2020
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience ? everything else is only information!" (Albert
Einstein)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
konsult@xxxxxxxxxxxxxxx
Sent: Friday, 3 January 2025 19:44
To: midrange-l <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: SQL function WEEK_ISO for years beyond 2039
Modernizing program that creates/updates the calendar file for a client and
many planning functions are based on week number.
I want to use the SQL "week_iso" function and found that it works pretty
well for the next 10 to 15 years.
I have as input a date field (*ISO) format and expect a zoned two digit
value in response.
As follows:
"exec sql set :weeknum = week_iso(:datum);"
The problem occurs when generation data further on in the future like dates
in the year 2040 and beyond. Then I get a RNQ0114 error with text indication
that year cannot be beyond 2039.
I have been searching for a workaround but have yet to find one.
/Ake Olsson/
--
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.
midrange.com
