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

Tim,

Isn't an easy solution for this prroblem, specialy because there is not a pattern, not at least one that you can idetify easily (maybe with some fractal math you'll find it :-))

My approach will be to create a cursor where temporarily store the different parts of the character to date field, then to create a pattern I will atomize the diferent parts of the string, store day, month and year into separate fields of this cursor using the PL/SQL string functions, then will be a lot easier to convert it to a date type by concatenating the different pieces together and use to_date() function. The problem that I see is that you have to make many assuptions in order to cover all the different possibilities on which a string may represent a date.