Steve, Check the DATE FORMAT, DATE SEQUENCE combination.
Why not say BETWEEN in your first statement? Wouldn't it be simpler to use a view for the command which doesn't work and correctly alias the tables as per the RBASE way? Just some thoughts. Claudine :) -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Wills, Steve Sent: Thursday, February 07, 2008 2: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 --- RBASE-L =======================3D======================= D= TO POST A MESSAGE TO ALL MEMBERS: Send a plain text email to [email protected] (Don't use any of these words as your Subject: INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH, REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP) =======================3D======================= D= TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [email protected] In the message SUBJECT, put just one word: INTRO =======================3D======================= D= TO UNSUBSCRIBE: Send a plain text email to [email protected] In the message SUBJECT, put just one word: UNSUBSCRIBE =======================3D======================= D= TO SEARCH ARCHIVES: Send a plain text email to [email protected] In the message SUBJECT, put just one word: SEARCH-n (where n is the number of days). In the message body, place any text to search for. =======================3D======================= D=
