varchar to timestamp

[The Little Guy]

I currently have a varchar for my timestamp, I haven't changed it to a timestamp field because I wasn't sure if it would mess up my dates, the dates are in a perfect timestamp format though.

 

So, if I update the datatype of that field will I get an error, or worse, and invalid date?

[requinix]

I think it will work, but try it on a small test table.

CREATE TABLE test (a VARCHAR(100));
INSERT INTO test VALUES ("2011-08-18 12:34:56");

ALTER TABLE test MODIFY COLUMN a TIMESTAMP;

SELECT * FROM test;

 

If it doesn't work then

ALTER TABLE test ADD COLUMN b TIMESTAMP AFTER a;
UPDATE test SET b = a; -- or whatever transformation is necessary
ALTER TABLE test DROP COLUMN a;
ALTER TABLE test CHANGE COLUMN b a TIMESTAMP;

 

No promises on the exact SQL, I get mixed up between MySQL and MS SQL sometimes.

[fenway]

Always good to add an extra column, test that (with an equality, make sure there all the rows "match"), and then drop the old one.

[The Little Guy]

Always good to add an extra column, test that (with an equality, make sure there all the rows "match"), and then drop the old one.

 

That is exactly what I did (b4 I read this post)!

 

Thanks!