Re: Insert records with date type in a Oracle database

[ Follow Ups ] [ Post Followup ] [ SQL Script Builder Forum ]

Posted by Dave on July 25 2005 21:02:34:

In Reply to: Insert records with date type in a Oracle database posted by Javier Garnica on July 05 2005 20:10:48:

Hi Javier,

Version 1.24 is out !

The ability to format dates have been added to SQL Script Builder so now yes you can generate statements as you wanted in Oracle with to_date to correctly to update the date type fields.

Dave

> I am trying to insert records in a Oracle database from an Access Odbc connection that contains fields of date type, and the script generated contains statements like this:
>
> INSERT INTO MF_DOCUMENT VALUES('001168-M','00000001.TIF',
> '20','29/05/2003','29/05/2003','1','2','35','864513');
>
> when I run the script Oracle SQL*PLUS console I receive the following error message in every insert statement:
>
> '20','29/05/2003','29/05/2003','1','2','35','864513');
> *
> ERROR AT line 2:
> ORA-01843: not to valid month
>
>
> And when I try this insert statement by hand with the to_date function it works ok.
> INSERT INTO MF_DOCUMENT VALUES('001168-M','00000001.TIF',
> '20',to_date('29/05/2003', 'DD/MM/YYYY'),to_date('29/05/2003', 'DD/MM/YYYY'),'1','2','35','864513');
>
> Is there any way to generate the script for Oracle correctly to update the date type fields?
>

Follow Ups:

Post a Followup

* : (Required)

* Name:
E-Mail:

* Subject:

* Comments:
> Hi Javier, > > Version 1.24 is out ! > > The ability to format dates have been added to SQL Script Builder so now yes you can generate statements as you wanted in Oracle with to_date to correctly to update the date type fields. > > Dave > > > I am trying to insert records in a Oracle database from an Access Odbc connection that contains fields of date type, and the script generated contains statements like this: > > > > INSERT INTO MF_DOCUMENT VALUES('001168-M','00000001.TIF', > > '20','29/05/2003','29/05/2003','1','2','35','864513'); > > > > when I run the script Oracle SQL*PLUS console I receive the following error message in every insert statement: > > > > '20','29/05/2003','29/05/2003','1','2','35','864513'); > > * > > ERROR AT line 2: > > ORA-01843: not to valid month > > > > > > And when I try this insert statement by hand with the to_date function it works ok. > > INSERT INTO MF_DOCUMENT VALUES('001168-M','00000001.TIF', > > '20',to_date('29/05/2003', 'DD/MM/YYYY'),to_date('29/05/2003', 'DD/MM/YYYY'),'1','2','35','864513'); > > > > Is there any way to generate the script for Oracle correctly to update the date type fields? > > >

Optional Link URL:
Link Title:
Optional Image URL:

Enter the code shown :

[ Follow Ups ] [ Post Followup ] [ SQL Script Builder Forum ]