I’m trying to change a column from DATE to DATETIME in mySQL using sqlfiddle. But I’m just getting syntax errors, when looking at other stack overflow answers it seems like this method should work? What am I doing wrong? Is the only way (is it safer?) to add a new column, update new column with old and drop the old one?
My current method (assume there is data already in the table after creation)
CREATE TABLE re ( file_id INT NOT NULL AUTO INCREMENT PRIMARY KEY, mydate DATE NOT NULL); ALTER TABLE re alter column "mydate" DATETIME NOT NULL
The above doesn’t work on sqlfiddle but it’s the most common answer, other than the previous way. Am I doing something wrong?
There is, at least, one errors in you question:
AUTO INCREMENTshould have an underscore between AUTO and INCREMENT.
ALTER TABLEstatement should be written with
MODIFY COLUMN, and not with
ALTER COLUMN. (This is a possible bug in MySQL?)
I created a bug-report for this: https://bugs.mysql.com/bug.php?id=109461
P.S.: the time part of the DATETIME field will be set to ’00:00:00′, see: DBFIDDLE