Changing column types

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
f1nutter
Forum Contributor
Posts: 125
Joined: Wed Jun 05, 2002 12:08 pm
Location: London

Changing column types

Post 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.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
f1nutter
Forum Contributor
Posts: 125
Joined: Wed Jun 05, 2002 12:08 pm
Location: London

Post 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.
Post Reply