Re: SQL*Loader and Date conversions/masks

Hi,
Look at Scott's reply.

--
Best regards, Dmitry.
www.ispirer.com/chyfo.html - Database migration tool.

In article <961sb6$r44$1_at_nnrp1.deja.com>,
  alvie_at_my-deja.com wrote:


> Hi.

> If I exclude the hh:mm:ss I get this error:


> ORA-01830: date format picture ends before converting entire input

> string

>

> this is very frustrating - I'm need to get this data loaded.  thanks

> for your help.

>

> In article <961fjh$evg$1_at_nnrp1.deja.com>,

>   Ispirer <ispirer_at_ispirer.com> wrote:

> > Hi,

> >

> > In control file you must define date format of a text file, not a

> > SYSDATE or something else.

> >

> > You should set:

> >

> > for date format: 2000-11-22 12:28:46.790

> >

> > LOAD DATA

> >  INFILE 'd:\data_loads\load.txt'

> >  INTO TABLE demo.test1

> >  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING

 NULLCOLS


> >  (id_test1

> >  , date_created date "YYYY-MM-DD"

> >  , date_modified date "YYYY-MM-DD")


> >

> > If SQLLoader will complain, add second, minutes. Oracle DATE format

> > always contain seconds, minutes. But in your application you can not

> > use them.

> >

> > Best regards, Dmitry.

> >

> > In article <961e7n$dlu$1_at_nnrp1.deja.com>,

> >   alvie_at_my-deja.com wrote:

> > > Hi all -

> > > I have a table with date fields that default to SYSDATE.  I'm

 trying
 to


> > > load data from SQL Server into the these date fields where the

 dates


> > > look like this:

> > >

> > > "2000-11-22 12:28:46.790"

> > >

> > > I'd like the dates to be in the format that SYSDATE creates


 (mmddyyy)


> > >

> > > My control file looks like this:

> > >

> > > LOAD DATA

> > > INFILE 'd:\data_loads\load.txt'

> > > INTO TABLE demo.test1

> > > FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING

 NULLCOLS


> > > (id_test1

> > > , date_created date "ddmmmyy"

> > > , date_modified date "ddmmmyy")

> > >

> > > The log reports the following:

> > > Record 1: Rejected - Error on table DEMO.TEST1, column

 DATE_CREATED.


> > > ORA-01843: not a valid month

> > >

> > > any ideas?

> > >

> > > tia

> > > as

> > >

> > > Sent via Deja.com

> > > http://www.deja.com/

> > >

> >

> > Sent via Deja.com

> > http://www.deja.com/

> >

>

> Sent via Deja.com

> http://www.deja.com/

>



Sent via Deja.com
http://www.deja.com/