Character to Date

[Guest_imported]

How do I convert a character field to a date field? I created a table with a date. However I defined the field as a char field because I did not know I would be doing any manipulation with it. Now I need to convert the field to a date without having to redo the table. Help me please...

 

[carp]

What RDBMS are you using? [sig][/sig]

 

[carp]

You cannot directly change the datatype from char to date if there is data in the column.
There are a couple of ways to take care of the problem:

1. If you are using Oracle 8.1, you can add a column to the table, update the table so that all of the rows have the new column populated with the date equivilent of the character column you currently have. Then drop the character column.

2. For pre-8.1, you can do all of the above except dropping the character column. You COULD just set the character column to all NULL values and just ignore it.
However, you could take a cleaner approach:
a. Create a temporary table populated by your table's primary key column and the character column.
b. Set all of the values in the character column to NULL.
c. Now that there is no data in the character column, you can ALTER the table to MODIFY the column to a DATE datatype.
d. Update your date column with the date equivilent of the char values in the temporary table created in Step a. Do this by joining your table with the temp table (this is why you included the primary key column in the temporary table).
e. Finally, after you verify the correctness of your data, drop the temporary table. [sig][/sig]