Scott, Aby,
TO_DATE is the oracle date format.
{ts ... is the ODBC date format
Scott is using an ODBC driver to access Oracle.
I suggest switching to the native driver.
Brendan
-----Original Message-----
From: Aby Mathew [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 16, 1999 11:59 AM
To: 'Scott Johnson';
'[EMAIL PROTECTED]'
Subject: Re: [ND] Help with Oracle RDBMS/CSpDatetime
usage
Scott,
When I look at the RBBMS Service log, for a date field I
find something like
this:
>> TO_DATE('1999/12/15 00:00:00','YYYY/MM/DD HH24:MI:SS') <<
Yes, ND creates this TO_DATE() with matching format and
value strings. I am
confused by what I see in your log :
>> {ts '1999-12-15 00:00:00.000'} <<
I don't know what if means. Probably would require some
investigation.
Aby
> -----Original Message-----
> From: Scott Johnson [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, December 16, 1999 11:28 AM
> To: Aby Mathew
> Subject: Re: [ND] Help with Oracle RDBMS/CSpDatetime usage
>
>
> Thanks for responding. I believe the issue isbased on
> milliseconds. But, I do
> not know how to resolve. Any ideas?
>
>
> Aby Mathew 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][OracleOCI]ORA-01830:
> date format picture ends before converting entire input
string.
>
> How does ND translate "{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 execute 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?
>
>
>
> --
> scott
>
>
_________________________________________________________________________
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]
