alter field

[eduac]

guys,

i have the following problem:

I need to change a field in the format int(30) - unixtimestamp for the date format, but this table have many rows and cannot use DROP TABLE and create another one or to change the format without update the values.

So, exists some way to use ALTER TABLE to change the field format and using a UPDATE to modify each value.. later???

Here is the example:

current way ->

mydate - int(30) not null

109555465

change to ->

mydate - date not null

2003-08-07

i hope that i have explained well...

[BlueMeteor]

if you change your fields int to date ,you will get 0 value every record....It's not your want

so...is my plan,maybe not the best one , but a good one

Use transfer:VARCHAR

ALTER TABLE URtableName CHANGE mydate mydate varchar(30)
//change to varchar first

UPDATE URtableName SET mydate= FROM_UNIXTIME( mydate)
//update your value to 'YYYY-MM-DD' ^_ you had better select your table's records after this step,check it!

ALTER TABLE URtableName CHANGE mydate mydate date
//now you can chang your field-type

hope that helps a little

[BlueMeteor]

sorry....I forgot something important.....

The database I used is MySQL...

hope you same