× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Hi,

you have a very common problem!
Your date fields in RPG are defined with date format *ISO.
The valid range for this date format between '0001-01-01' and '9999-12-31'

SQL do not care about the date formats specified in the D- or H-specs.
The SQL precompiler comments the SQL statements out and replaces them
with API calls. For all host variables used in these SQL statements
work fields are created. If date fields are used, they do not adopt the
date format from the original field, but the format specified in the
Compile command CRTSQLRPGI option DATFMT or in a SET OPTION statement in
your program.

The default value for the date format is *JOB, and the job date format uses
normally only a 2 digit year. This means the valid range for these
dates is between 1940-01-01 and 2039-12-31.

If in your file are date fields that contain '0001-01-01', this date
will be out of valid range and cause this error.

For more information look at the following Redbook:
Modernizing IBM eServer iSeries Application Data Access - A Roadmap
Cornerstone
http://www.redbooks.ibm.com/abstracts/sg246393.html?Open
Chapter 7:    Embedded SQL
Chapter 9.3.: Date calculation with RPG and SQL

Birgitta

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Mike Wills
Gesendet: Mittwoch, 23. März 2005 19:53
An: Midrange_RPG400_L
Betreff: SQL Date Type SQLRPGLE Program Error


I am hoping someone can help me understand why my code isn't working.
On my file, there is some SQL date data types defined. For example:

D gResultDs     E DS                  EXTNAME(DBAPAPI)
75=D DAPICMPNY                      4B 0
76=D DAPIVENDOR                     9A
77=D DAPIINVC                      22A
78=D DAPISUFFIX                     4B 0
79=D DAPICNCLSQ                     4B 0
80=D DAPICNCLDT                    10D   DATFMT (*ISO-)
81=D DAPIBTCHNM                     9B 0
82=D DAPIBTCHDT                    10D   DATFMT (*ISO-)
83=D DAPIVCHNBR                    10A
84=D DAPIATHCD                      3A
85=D DAPIPRCLVL                     5A
86=D DAPIACCRCD                     4A
87=D DAPIINVOTY                     1A
88=D DAPIINVCRR                     5A
89=D DAPIPYCRRN                     5A
90=D DAPIINSTIN                    10D   DATFMT (*ISO-)
91=D DAPIPRFRLC                     4A
92=D DAPIPNMBR                     14A

I am doing a "select *" and putting the results in gResultDs. I am
getting a SQLSTT of 22008 and SQLCOD of -183. The text for the error
is: Datetime field overflow occured; for example an arithmetic
operation on a date or timestamp has a result that is not within the
valid range of dates.

Using UPDDTA, I don't see any invalid dates. There are a couple that
are "0" (0001-01-01), all of the rest are within this last year or so.
Any ideas for me?

--
Mike Wills
Midrange Programmer/Lawson Administrator
koldark@xxxxxxxxx
http://www.mikewills.name
Want Gmail? Email koldark+gmail@xxxxxxxxx to get on my waiting list.
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.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 on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.