OCI and date fields

From: Ron Lamb <rlamb_at_columbus.rr.com>
Date: Tue, 25 Jan 2000 15:05:24 -0500
Message-ID: <86kv95$f64$1_at_ssauraaa-i-1.production.compuserve.com>

[Quoted] I am having trouble getting an insert into a table that contains a date field [Quoted] to work under OCI for Oracle 7.3.4.

The date format is of the form DD-MON-YYYY HH24:MI:SS

Under Sql Plus I can just enter

ALTER SESSION set NSL_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

before insertions and then do the inserts

ex:

    insert into custbase(cname, cnum, cbase, ecode, crtdate)     values ('bob', 100, 10, 10, '20-JAN-2000 13:23:07')

If I try to insert the same record using OCI i get

ORA-01858: A non-numeric character was found where a numeric was expected. I have also ran the same ALTER SESSION command before the insert. It looks like the ALTER SESSION statement is being ignored.

the definition of custbase is

CREATE TABLE custbase

    (
    cname VARCHAR(256),

    cnum       INTEGER,
    cbase     INTEGER,
    ecode     INTEGER,

    crtdate DATE,
    constraint pk_custbase PRIMARY KEY (cname, cnum, btype));

Also is there a way to get Oracle to figure out the date without having to specify it each time.

I have a flat text file that contains differently formatted dates depending on the input source.

The following representation of the date January 20, 2000 13:23:07 can be seen in the file.

01/20/00 12:23:07PM
20-Jan-00 13:23:07
20-Jan-2000 13:23:07
20 Jan 2000 13:23:07
January 20, 2000 13:23:07

Microsoft SQL Server seems to do a very good job at inserting the date

Ron Received on Tue Jan 25 2000 - 21:05:24 CET