How to change column type in mySQL from DATE to DATETIME-CodePudding

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?

CodePudding user response：

There is, at least, one errors in you question:

AUTO INCREMENT should have an underscore between AUTO and INCREMENT.
The ALTER TABLE statement should be written with MODIFY COLUMN, and not with ALTER COLUMN. (This is a possible bug in MySQL?)

see: DBFIDDLE

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