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.
Changing column types
Moderator: General Moderators
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
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):
You could also change the format to a timestamp.
Mac
Code: Select all
ALTER TABLE `db_name`.`table_name` CHANGE `column_name` `column_name` DATE DEFAULT '0000-00-00' NOT NULLMac
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.
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.