 | | | Subject: Re: Date conversion question | Subject: Re: Date conversion question 2007-10-03 - By Rumpi Gravenstein
Back
Harvinder,
Are these valid dates? Some clearly aren't ( "0"). I can think of at least
two ways to get this loaded:
1. Use a column expression (http://tinyurl.com/29k93m) within your
Sqlloader control file to convert the data into a valid date.
2. Load the data into a staging table that has this column defined as a
VARCHAR2. Then write a PL/SQL procedure that moves the data from the
staging table to the real table. The procedure should include the necessary
logic to convert this column into a recognized Oracle date format.
On 10/3/07, Harvinder Singh <Harvinder.Singh@(protected)> wrote:
>
> Hi,
>
> We have a flat file that contains a column and the values are stored as:
> 991123
> 421
> 60823
> 0
> 70930
>
> Now we are moving data from flat file to table and this column will be
> inserted into Date dataype column, What is the way to insert into this
> table and avoid "ORA-01821 (See http://ORA-01821.ora-code.com): date format not recognized" errors.
>
> Thanks
> --Harvinder
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
--
Rumpi Gravenstein
<div>Harvinder,</div>
<div> </div>
<div>Are these valid dates? Some clearly aren't ( "0").
I can think of at least two ways to get this loaded:</div>
<div> </div>
<div>1. Use a column expression (<a href="http://tinyurl.com/29k93m">http
://tinyurl.com/29k93m</a>) within your Sqlloader control file to convert
the data into a valid date.</div>
<div>2. Load the data into a staging table that has this column defined
as a VARCHAR2. Then write a PL/SQL procedure that moves the data from the
staging table to the real table. The procedure should include the
necessary logic to convert this column into a recognized Oracle date format.
<br> </div>
<div><span class="gmail_quote">On 10/3/07, <b class="gmail_sendername"
>Harvinder Singh</b> <<a href="mailto:Harvinder.Singh@(protected)"
>Harvinder.Singh@(protected)</a>> wrote:</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0
.8ex; BORDER-LEFT: #ccc 1px solid">Hi,<br><br>We have a flat file that contains
a column and the values are stored as:<br>991123<br>421<br>60823
<br>0<br>70930<br><br>Now we are moving data from flat file to table and this
column will be<br>inserted into Date dataype column, What is the way to insert
into this<br>table and avoid "ORA-01821 (See http://ORA-01821.ora-code.com): date format not recognized"
errors.
<br><br>Thanks<br>--Harvinder<br><br>--<br><a href="http://www.freelists.org
/webpage/oracle-l">http://www.freelists.org/webpage/oracle-l</a><br><br><br><
/blockquote></div><br><br clear="all"><br>-- <br>Rumpi Gravenstein
|
|
|