When using dates in "character" form, you may use '0001-01-01' instead of DATE('0001-01-01'). The SQL engine "knows" it is a date. Running the SQL statement "select * from sometable where somedate > '0001-01-01'" works for me. Can we assume that XTBUSDATE is an actual date field (SQL type date or DDS type L)? You can reduce your sql code by doing this, also: "and XTBUSDATE between '2001-12-01' and '2001-12-31'". HTH, Loyd -----Original Message----- From: Fisher, Don [mailto:Dfisher@roomstoreeast.com] Sent: Monday, January 07, 2002 1:55 PM To: 'firstname.lastname@example.org' Subject: SQL Error -183 I have an RPGIV program using dynamically constructed SQL over a file returning the -183 error code ("Result of date or timestamp expression not valid") on a FETCH command. The job log indicates a field in the file being queried has invalid data in a field that is a DATE data type. However, every record in the file contains the value "0001-01-01" in this field, which is a valid value according to the message text. The SQL statement being executed successfully is as follows: Select A.* from BNBXTTP A join UTLMPRP on DIGITS(XTLOCATION) = SUBSTR(MPPVLU,2,4) where MPPGM = 'BNB001C2' and MPCSEQ = 00010 and MPPSEQ = 55 and XTBUSDATE >= DATE('2001-12-01') and XTBUSDATE <= DATE('2001-12-31') order by XTLOCATION,XTBUSDATE,XTACCTNBR,XTTRANCODE,XTTRANAMT,XTERRREF The "DATE" functions were added as a result of something I read in the SQL programming manual, but didn't change the result. The FETCH command is as follows: Fetch From BNBTransWithErrors into :BNBXTTPDS BNBXTTPDS is an externally described data structure using the BNBXTTP file as the format. I'm wondering if we're missing a PTF here, but I don't know. We are at V4R1M0 (yes, I know...) Any help will be greatly appreciated. Donald R. Fisher, III Project Manager The Roomstore Furniture Company (804) 784-7600 ext. 2124 DFisher@roomstoreeast.com _______________________________________________ This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@midrange.com To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l or email: MIDRANGE-Lemail@example.com Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.
As an Amazon Associate we earn from qualifying purchases.
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.