alter table query

[FRED]

hi all,

i have a table that has a column date varchar(20) and now i learned some
more about mysql I want to make it date date but if I do an alter table on
it what will it do to my existing data which currently is in the format 11
June 2004 (but in a varchar type).

many thanks!

F

[Aggro]

FRED wrote:

i have a table that has a column date varchar(20) and now i learned some
more about mysql I want to make it date date but if I do an alter table on
it what will it do to my existing data which currently is in the format 11
June 2004 (but in a varchar type).

I suggest that you use:

Warning, I wrote the next queries from my memory, I haven't tested them
so they might contain syntax errors or work incorrectly, but you should
get the idea of what you need to do:

alter table tablename add temp_column date;
update tablename set temp_column = xxxxx;
alter table tablename change old_date_column new_column_name date;
update tablename set new_column_name = temp_column;
alter table tablename drop temp_column;

xxxx depends on what version of MySQL you have

If you have MySQL 4.1.1. you can use STR_TO_DATE() function.
http://dev.mysql.com/doc/mysql/en/Da...functions.html

If you don't, then you have to parse it using string functions, which
will do a little more job. ( You would need to make your currect date in
format yyyy-mm-dd and especially the month name will give us some extra
work. )

http://dev.mysql.com/doc/mysql/en/String_functions.html