|
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.
As an Amazon Associate we earn from qualifying purchases.
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.
midrange.com
