By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,092 Members | 1,650 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,092 IT Pros & Developers. It's quick & easy.

alter table query

P: n/a
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

Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.