Re: Insert records with date type in a Oracle database

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

Posted by Dave on July 11 2005 21:57:20:

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,

Dealing with dates in SQL is not easy, especially with Oracle because it can keep dates in a wide variety of forms.

To answer your question No, not yet, because SQL Script Builder cannot determine the date format of your Oracle system, your idea could be a future improvement of SQL Script Builder, but the user will have to specify its date format.

For the moment one of the solutions would be to change the way dates are stored before executing the script using the following command in SQL*PLUS console :

alter session set NLS_DATE_FORMAT = 'dd/mm/yyyy';

Thank you for your message, it is greatly apreciated.

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, > > Dealing with dates in SQL is not easy, especially with Oracle because it can keep dates in a wide variety of forms. > > To answer your question No, not yet, because SQL Script Builder cannot determine the date format of your Oracle system, your idea could be a future improvement of SQL Script Builder, but the user will have to specify its date format. > > For the moment one of the solutions would be to change the way dates are stored before executing the script using the following command in SQL*PLUS console : > > alter session set NLS_DATE_FORMAT = 'dd/mm/yyyy'; > > Thank you for your message, it is greatly apreciated. > > 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 ]