Aby Mathew <[EMAIL PROTECTED]> wrote:
>> I assumed that ND was doing the to_date conversions.
>> Apparently, I am wrong.
>
>AFAIK, ND does a fairly decent to_date() conversion when you use date
>fields. The error message you are getting seems to be a clear indication of
>ND attempting a TO_DATE(), but there being some mismatch between the format
>string and the value string.
>
>I would turn the TERSE_DEBUG ON on the RDBMS service and look at the log to
>see what SQL is finally going to Oracle.
>
I did.
The value of timestampStr is a valid date/time combo.
Looking at the log output of this DO insert shows the following:
execute insert on column: IDP=idp_seq.nextval
execute insert on column: UPGRADE_PLAN=7.9
execute insert on column: DESCRIPTION=111
execute insert on column: CREATE_DATE=sysdate
execute insert on column: LAST_MODIFIED_BY=sjohnso2
execute insert on column: LOAD_DATE=12/15/1999 00:00:00.000
execute INSERT: |INSERT INTO LOAD.IDP
( IDP, UPGRADE_PLAN, DESCRIPTION, CREATE_DATE,
LAST_MODIFIED_BY, LOAD_DATE ) VALUES ( idp_seq.nextval, ?, ?
,sysdate, ?, {ts '1999-12-15 00:00:00.000'} )|
...
Vendor message 2: [Oracle][ODBC Oracle Driver][Oracle OCI]ORA-01830: date format
picture
ends before converting entire input string.
How does ND translating "{ts '1999-12-15 00:00:00.000'}" ?
Once I know this, I can modify the Oracle parameter NLS_DATE_FORMAT
to match it.
For those familiar with Oracle, I include two to_date() function calls via SQLPlus.
The only difference is that one includes a milliseconds portion.
SQL> insert into load.idp
2 (idp
3 ,create_date)
4 values (load.idp_seq.nextval
5 ,to_date('1999-12-15 00:00:00','YYYY-MM-DD HH24:MI:SS'));
1 row created.
SQL> insert into load.idp
2 (idp
3 ,create_date)
4 values (load.idp_seq.nextval
5 ,to_date('1999-12-15 00:00:00.000','YYYY-MM-DD HH24:MI:SS'));
,to_date('1999-12-15 00:00:00.000','YYYY-MM-DD HH24:MI:SS'))
*
ERROR at line 5:
ORA-01830: date format picture ends before converting entire input string
Yes, I know, my format does not include a millisecond portion. That's the kicker,
Oracle DATE format strings do not have a definable way to specify milliseconds.
I included the second insert statement on the remote chance that Oracle would
accept a fractional second. Interesting, the error I received is the same error
that ND published after attempting to executing an insert.
How can I match the ND and Oracle datetime formats?
Can I set ND datetime formats to exclude milliseconds when calling an Oracle
DO?
>
>Aby
>TeamND
>
>> -----Original Message-----
>> From: scott johnson [mailto:[EMAIL PROTECTED]]
>> Sent: Wednesday, December 15, 1999 1:33 PM
>> To: [EMAIL PROTECTED]
>> Subject: [ND] Help with Oracle RDBMS/CSpDatetime usage
>>
>>
>>
>> Having problems inserting a datetime value into an Oracle
>> timestamp datatype.
>> Getting an Oracle error,
>>
>> "1830: date format picture ends before converting
>> entire input string"
>>
>> I assumed that ND was doing the to_date conversions.
>> Apparently, I am wrong.
>> Anyone conquered this CSpdatetime/Oracle issue?
>>
>>
>> Code example:
>> ...
>>
>> CSpMultiSQL idpDO = (CSpMultiSQL)
>> CSpider.getDataObject("idpAdmin");
>> CSpTransaction theTrans = new CSpTransaction();
>>
>> ..
>>
>> String dateStr = getDisplayFieldValue("txStartDate").stringValue();
>> String timeStr = getDisplayFieldValue("cbStartTime").stringValue();
>> String timestampStr = new String(dateStr + " " + timeStr);
>>
>> CSpDatetime theTS = new CSpDatetime();
>> try
>> {theTS = new CSpDatetime(timestampStr);}
>> catch (CSpDatetimeParsingException ex)
>>
>> ..
>>
>> idpDO.setValue("LOAD_IDP_LOAD_DATE", theTS);
>>
>> ..
>>
>> theTrans.addDataObject (idpDO
>> ,idpDO.constructDefaultInsertDBRequest()
>> );
>>
>> command = theTrans.execute();
>> ______________________________________________________________
>> ___________
>>
>> For help in using, subscribing, and unsubscribing to the discussion
>> forums, please go to:
>> http://www.netdynamics.com/support/visitdevfor.> html
>>
>> For dire
>> need help, email: [EMAIL PROTECTED]
>>
_________________________________________________________________________
For help in using, subscribing, and unsubscribing to the discussion
forums, please go to: http://www.netdynamics.com/support/visitdevfor.html
For dire need help, email: [EMAIL PROTECTED]
