Re: SQL*Loader and Date conversions/masks

'mm' is for the month part of the date field, for minutes you need to use 'mi' (I know, I got bit by this myself).

<alvie_at_my-deja.com> wrote in message news:961r91$q2t$1_at_nnrp1.deja.com...
> Thanks for the response. I tried even with the minutes and seconds.
> Now I'm getting this error:
>
> date_created date "YYYY-MM-DD hh:mm:ss.ms"
>
> ORA-01810: format code appears twice
>
>
>
> 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/
Received on Fri Feb 09 2001 - 23:08:23 CET