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


  1.  Home
  2. RPG400-L
  3. March 2005

Re: AW: SQL Date Type SQLRPGLE Program Error

Thanks guys! That works for me. I guess I didn't realize that the
default was dealing in 2-digit years. I used the SET OPTION SQL stmt
and i got it to work.

Boy, I own tons of people a beer if I ever go to a COMMON.


On Thu, 24 Mar 2005 14:07:37 +0100, HauserSSS <Hauser@xxxxxxxxxxxxxxx> wrote:
> Hi,
> 
> the SET OPTION statement can be used instead of the compile option.
> If you set the DATFMT in the SET OPTION statement to either
> *ISO, *USA, *EUR or *JIS it will work.
> These date formats all have a 4 digit year. SQL (not the precomiler)
> does not care about any date format, because the date is stored as
> a 4 Byte integer value, that represents the calculated number of days
> from the '0001-01-01'.
> 
> But keep in mind, the SET OPTION statement can be used only once in
> a source member. Even if it contains several independent procedures.
> If the SET OPTION statement is specified in one procedure, it is
> valid for all other procedures.
> 
> Birgitta
> 
> -----Ursprüngliche Nachricht-----
> Von: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von rob@xxxxxxxxx
> Gesendet: Donnerstag, 24. März 2005 13:46
> An: RPG programming on the AS400 / iSeries
> Betreff: Re: AW: SQL Date Type SQLRPGLE Program Error
> 
> 
> Would SQL's equivalent of the H spec help?
>      C/EXEC SQL
>      C+ Set Option
>      C+     Naming    = *Sys,
>      C+     Commit    = *None,
>      C+     UsrPrf    = *User,
>      C+     DynUsrPrf = *User,
>      C+     Datfmt    = *iso,
>      C+     CloSqlCsr = *EndMod
>      C/END-EXEC
> 
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
> 
> "HauserSSS" <Hauser@xxxxxxxxxxxxxxx>
> Sent by: rpg400-l-bounces@xxxxxxxxxxxx
> 03/23/2005 11:44 PM
> Please respond to
> RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
> 
> To
> "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
> cc
> 
> Subject
> AW: SQL Date Type SQLRPGLE Program Error
> 
> 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.
> 
> --
> 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.
> 
> --
> 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.
> 
> --
> 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.
> 
> 


-- 
Mike Wills
Midrange Programmer/Lawson Administrator
koldark@xxxxxxxxx
http://www.mikewills.name
Want Gmail? Email koldark+gmail@xxxxxxxxx to get on my waiting list.

As an Amazon Associate we earn from qualifying purchases.

This thread ...
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.