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=======================3
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=======================3
D=
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: INTRO
=======================3D=======================3
D=
TO UNSUBSCRIBE:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: UNSUBSCRIBE
=======================3D=======================3
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=======================3
D=
