Steve, 1) Are the Oracle columns actually being seen as DATETIME datatypes? 2) I would recommend putting the ".#NOW" on the right side of the comparison. Dotted variables always belong on the right. 3) In your posting, there is an extra plus sign in the WHERE clause of the BROWSE. Maybe, though, that was just the way your message wrapped here. I see ".#NOW > R_AWARD_AMOUNT_INFO.CURRENT_FUND_EFFECTIVE_DATE + AND +" but when it is quoted here it wraps differently.
Emmitt Dove Manager, DairyPak Business Systems Evergreen Packaging, Inc. [EMAIL PROTECTED] [EMAIL PROTECTED] (203) 643-8022 -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Wills, Steve Sent: Thursday, February 07, 2008 3:51 PM To: RBASE-L Mailing List Subject: [RBASE-L] - DATETIME Comparison Q I am having troubles making comparisons with values in a couple of DATETIME fields from a table whose structure is shown below. It is a copy of an ORACLE-based table, copied while SATTACHed to that DB (but I don't know if that has any bearing on my problem). This statement works: SELECT * FROM R_AWARD_AMOUNT_INFO WHERE .#NOW > CURRENT_FUND_EFFECTIVE_DATE AND .#NOW <= OBLIGATION_EXPIRATION_DATE This one does not, run either from command line (w/o continuator) or from command file : BROWSE + R_AWARD_AMOUNT_INFO.OBLIGATED_CHANGE AS AMOUNT, + R_AWARD_HEADER.RH_TITLE AS PROJECT_TITLE, + R_AWARD_AMOUNT_INFO.CURRENT_FUND_EFFECTIVE_DATE AS CURR_FND_EFF_DT, + R_AWARD_AMOUNT_INFO.OBLIGATION_EXPIRATION_DATE AS OBLG_EXPIRE_DT + FROM + R_AWARD_HEADER, + R_AWARD_AMOUNT_INFO + WHERE + R_AWARD_AMOUNT_INFO.MIT_AWARD_NUMBER = R_AWARD_HEADER.MIT_AWARD_NUMBER + AND + R_AWARD_AMOUNT_INFO.SEQUENCE_NUMBER = R_AWARD_HEADER.SEQUENCE_NUMBER + AND + .#NOW > R_AWARD_AMOUNT_INFO.CURRENT_FUND_EFFECTIVE_DATE + AND + .#NOW <= R_AWARD_AMOUNT_INFO.OBLIGATION_EXPIRATION_DATE Throwing this error: -ERROR- Invalid DATETIME value (2418) I have tried using various functions, "flip-flopping" the operands and operators, and I just can't figure out the problem. Any ideas? Thanks, Steve in Memphis -- *** TABLE INFO *** R>SET NAMEWIDTH 30 R>list table R_AWARD_AMOUNT_INFO Table: R_AWARD_AMOUNT_INFO No Lock(s) Descr: Server table - COEUS.OSP$AWARD_AMOUNT_INFO COEUS_ORACE No. Column Name Attributes --- ------------------ ------------------------------------------------------ 1 MIT_AWARD_NUMBER Type : TEXT 10 NOT NULL Consrnt: PRIMARY KEY 2 SEQUENCE_NUMBER Type : NUMERIC ( 4, 0) NOT NULL Consrnt: PRIMARY KEY 3 AMOUNT_SEQUENCE_NUMBER Type : NUMERIC ( 4, 0) NOT NULL Consrnt: PRIMARY KEY 4 ANTICIPATED_TOTAL_AMOUNT Type : NUMERIC (12, 2) 5 ANT_DISTRIBUTABLE_AMOUNT Type : NUMERIC (12, 2) 6 FINAL_EXPIRATION_DATE Type : DATETIME 7 CURRENT_FUND_EFFECTIVE_DATE Type : DATETIME 8 AMOUNT_OBLIGATED_TO_DATE Type : NUMERIC (12, 2) 9 OBLI_DISTRIBUTABLE_AMOUNT Type : NUMERIC (12, 2) 10 OBLIGATION_EXPIRATION_DATE Type : DATETIME 11 TRANSACTION_ID Type : TEXT 10 Index : SINGLE-COLUMN 12 ENTRY_TYPE Type : TEXT 1 13 EOM_PROCESS_FLAG Type : TEXT 1 14 UPDATE_TIMESTAMP Type : DATETIME 15 UPDATE_USER Type : TEXT 8 16 ANTICIPATED_CHANGE Type : NUMERIC (12, 2) 17 OBLIGATED_CHANGE Type : NUMERIC (12, 2) Current number of rows: 24596 J. Stephen Wills Program Manager, Research Informatics Office of the Vice Chancellor for Research University of Tennessee Health Science Center 62 S. Dunlap, Suite 400 Memphis, TN 38163 Office: 901-448-2389 FAX : 901-448-7133
