Page 1 of 1

Changing column types

Posted: Thu Jun 13, 2002 10:49 am
by f1nutter
Hi folks,

I have a MySQL database which I have created from exporting a (very large) MS Access db using MyODBC. Some of the column types are not what I would like.

For example, a column type in Access is date/time but the format is just a date (d mmm yyyy). When I print out the contents of the MySQL column, it has extended the format to 2002-06-13 00:00:00 (DATETIME).

How do I change the column type (with data in it) into another type, in this case DATE?

It is not as easy as starting again with the right type, as the last 5 years work will be wasted!

Thanks.

Posted: Fri Jun 14, 2002 2:22 am
by twigletmac
Don't worry you don't have to start again in order to change the column type. You just need to execute the something like the following SQL statement (edited of course to reflect your own table structure):

Code: Select all

ALTER TABLE `db_name`.`table_name` CHANGE `column_name` `column_name` DATE DEFAULT '0000-00-00' NOT NULL
You could also change the format to a timestamp.

Mac

Posted: Fri Jun 14, 2002 11:30 am
by f1nutter
Thanks Mac,

I am using DATE instead of TIMESTAMP because I am using dates back to 1890.

I had a look at the MySQL manual and found the ALTER syntax, an note that if you want to change a column type without changing the name (or specifying it twice) then use MODIFY.

Thanks for pointing me in the right direction.