Re: TO_DATE transformation

I got errors when I tried to convert it to my DD-MON-YYYY which is what we have?

It did not work either?

"samy atoui" <s.atoui_at_bigfoot.com> wrote in message news:3C9FB923.47C5B89B_at_bigfoot.com...
> Scott Mattes wrote:
> >
> > At a guess I would say that it isn't DD/MM but MM/DD (that is more
common
> > in the US at least). Check your data and see if the rejected records
have
> > values greater than 12 in that first spot. Or, it could just be bad
data.
> > Reviewing the bad record file you set up should resolve the difference.
> >
> > "Ryan Gaffuri" <rkg100_at_erols.com> wrote in message
> > news:a7oa79$jcv$1_at_bob.news.rcn.net...
> > > This is for SQLLDR but Im testing it with an insert...
> > >
> > > I have date data that is in a text file that comes in looking like the
> > > following 04/04/2001 which is I remember correctly(Im at home and dont
> > have
> > > what I wrote at work with me) is in the form DD/MM/YYYY
> > >
> > > Im trying to insert this kind of record and I get errors. Here is what
I
> > > did...
> > >
> > > Insert into Table(Date_Column)
> > > Values(TO_DATE('04/04/2001', DD/MM/YYYY);
> > >
> > > That does not work. I get errors. Cant remember what it was exactly...
> > > something about bad date format. I tried doing a to_char then a
to_date
> > and
> > > got errors with that too.
> > >
> > > What do I do?
> > >
> > >
> Hello Ryan,
> a quick test on my box:
> a) lets' check the settings of NLS_DATE_FORMAT:
> test_at_rh72_817> select sysdate from dual;
>
> SYSDATE
> ---------
> 26-MAR-02
> b) create a test table:
> test_at_rh72_817> create table date_test (a date);
>
> Table created.
> c) insert a test column:
> test_at_rh72_817> insert into date_test(a)
> 2 values( to_date('04/04/2002', 'DD/MM/YYYY') );
>
> 1 row created.
>
> I inserted the date column with function to_date because the format you
> want/need does not match the format of nls_date_format (DD-MON-RR).
> Note: the format mask is included in single quotes.
> for further info take a look at sql reference, functions to_date
> and the nls support guide
> Regards
> Manuela Mueller
Received on Mon Mar 25 2002 - 17:20:21 CST