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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 2000/03/24
Message-ID: <3cnmds01ii6qs2k2oe6l9424576rmoof6a@4ax.com>

A copy of this was sent to tim.mcconechy_at_runtime.dk (if that email address didn't require changing) On Fri, 24 Mar 2000 12:15:12 +0100, you wrote:

>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!!
>

another idea would be to use a plsql function like:

ops$tkyte_at_8i> create or replace function my2date( p_string in varchar2 ) return date
  2 as
  3 type array is table of varchar2(40) index by binary_integer;   4

  5    l_fmts    array;
  6    l_date    date default NULL;
  7    l_str     varchar2(255);
  8    l_fromStr array;
  9    l_toStr   array;
 10  begin
 11     l_fmts(1) := 'DD Mon';
 12     l_fmts(2) := 'DD-Mon-YYYY';
 13     l_fmts(3) := 'DD Month YYYY';
 14     l_fmts(4) := 'MM/DD-RR';
 15     l_fmts(5) := 'MM/DD-RR "w9"';
 16  
 17     l_fromStr(1) := '-Sept-'; l_toStr(1) := '-Sep-';
 18     l_fromStr(2) := '-June-'; l_toStr(2) := '-Jun-';
 19     /* ... and other things that need to be replaced ... */
 20  
 21     for i in 1 .. l_fmts.count
 22     loop
 23        begin
 24          l_date := to_date( p_string, l_fmts(i) );
 25          exit;
 26        exception
 27          when others then null;
 28        end;
 29     end loop;
 30  
 31     if ( l_date is NULL )
 32     then
 33          for j in 1 .. l_fromStr.count
 34          loop
 35              l_str := replace( p_string, l_fromStr(j), l_toStr(j) );
 36              for i in 1 .. l_fmts.count
 37              loop
 38                     begin
 39                       l_date := to_date( l_str, l_fmts(i) );
 40                       exit;
 41                     exception
 42                       when others then null;
 43                     end;
 44               end loop;
 45               exit when l_date is not null;
 46          end loop;
 47     end if;
 48  
 49     return l_date;

 50 end;
 51 /

Function created.

ops$tkyte_at_8i> create table t ( str varchar2(25) ); Table created.

ops$tkyte_at_8i> insert into t values ( '01 Aug' ); ops$tkyte_at_8i> insert into t values ( '01 Sep' ); ....
ops$tkyte_at_8i> insert into t values ( '15 Sep' );

ops$tkyte_at_8i> select str, my2date(str) from t;

STR                       MY2DATE(S
------------------------- ---------
01 Aug                    01-AUG-00
01 Sep                    01-SEP-00
03 July                   03-JUL-00
1-Aug-2000                01-AUG-00
1-Jul-2000                01-JUL-00
1-Oct-2000                01-OCT-00
1-Sep-2000                01-SEP-00
1-Sept-2000               01-SEP-00
1/2-00                    02-JAN-00
1/3-00                    03-JAN-00
1/3-00 w9                 03-JAN-00
10-Sep-2001               10-SEP-01
10/4-00                   04-OCT-00
12-Nov-2000               12-NOV-00
12/6-00                   06-DEC-00
14-Aug-2000               14-AUG-00
15 Aug                    15-AUG-00
15 August 2000            15-AUG-00
15 Sep                    15-SEP-00

>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.
>

-- 
http://osi.oracle.com/~tkyte/
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Received on Fri Mar 24 2000 - 00:00:00 CST