Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: TO_DATE transformation

Re: TO_DATE transformation

From: samy atoui <s.atoui_at_bigfoot.com>
Date: Tue, 26 Mar 2002 00:56:19 +0100
Message-ID: <3C9FB923.47C5B89B@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:56:19 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US