Re: OCI and date fields

[Quoted] Have you tried using the TO_DATE() function?

Ron Lamb wrote in message
<86kv95$f64$1_at_ssauraaa-i-1.production.compuserve.com>...
>I am having trouble getting an insert into a table that contains a date
field
>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 Thu Jan 27 2000 - 03:41:17 CET