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: Char to Date but column has many different date formats.

Re: Char to Date but column has many different date formats.

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 2000/03/24
Message-ID: <953896610.6438.0.pluto.d4ee154e@news.demon.nl>#1/1

One statement will be impossible.
Somehow you need to add where clauses like update ...
where length(moc29) = 6 and replace(substr(mod29,1,2), '0123456789','xxxxxxxxxx') = 'xx'

and so on.
You probably get the picture.
I don't envy you, because in the past I many times cleaned out similar messes.

Good Luck!!

Hth,
Sybrand Bakker, Oracle DBA
<tim.mcconechy_at_runtime.dk> wrote in message news:8bfhdo$am4$1_at_nnrp1.deja.com...
> Hi!
> I need to import a character column to a date column
> but the dates or in many different formats:
> Quite difficult because there are many different types of date formats:
>
> I don't think it can be done but maybe some one has a way..
>
> Example:
>
> 01 Aug
> 01 Sep
> 03 July
> 1-Aug-2000
> 1-Jul-2000
> 1-Oct-2000
> 1-Sep-2000
> 1-Sept-2000
> 1/2-00
> 1/3-00
> 1/3-00 w9
> 10-Sep-2001
> 10/4-00
> 12-Nov-2000
> 12/6-00
> 14-Aug-2000
> 15 Aug
> 15 August 2000
> 15 Sep
>
> I know the code should look something like:
> For 15 Sep
> update styles
> set mod003=to_date(moc029,'dd MON');
>
> For
> 15 August 2000
> set mod003=to_date(moc029,'dd month yyyy');
>
> But the problem is all will give the message
> *
> ERROR at line 2:
> ORA-01843: not a valid month
>
> Because this expects that all columns have the same date format.
>
> Is there some way I can only get the dates with the format
> without the error message and maybe create
> 1 update statement for each column...
>
> Thanks!!!!
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Mar 24 2000 - 00:00:00 CST

Original text of this message

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