MySQL: Insert datetime into other datetime field

问题:

I have a table with a DATETIME column. I would like to SELECT this datetime value and INSERT it into another column.

I did this (note: '2011-12-18 13:17:17' is the value the former SELECT gave me from the DATETIME field):

UPDATE products SET former_date=2011-12-18 13:17:17 WHERE id=1

and get

    1064 - You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version 
for the right syntax to use near '13:17:17 WHERE itemid=1' at line 1

Ok, I understand it's wrong to put an unquoted string in there, but is DATETIME just a string in the first place? What do I put in there? All I want is reliably transfer the existing value over to a new datetime field...

EDIT:

The reason I ask is: I have this special definition, DATETIME, and somehow I thought it gives me some security and other advantages when handling dates. Now it seems it is simply a specialized VARCHAR, so to speak.

Thanks for your answers, it seems this is indeed the intended behaviour.

回答1:

According to MySQL documentation, you should be able to just enclose that datetime string in single quotes, ('YYYY-MM-DD HH:MM:SS') and it should work. Look here: Date and Time Literals

So, in your case, the command should be as follows:

UPDATE products SET former_date='2011-12-18 13:17:17' WHERE id=1

回答2:

Try

    UPDATE products SET former_date=20111218131717 WHERE id=1

Alternatively, you might want to look at using the STR_TO_DATE (see STR_TO_DATE(str,format)) function.

回答3:

for MYSQL try this

INSERT INTO table1(myDatetimeField)VALUES(STR_TO_DATE('12-01-2014 00:00:00','%m-%d-%Y %H:%i:%s');

verification-

select * from table1
output- datetime= 2014-12-01 00:00:00

回答4:

If you don't need the DATETIME value in the rest of your code, it'd be more efficient, simple and secure to use an UPDATE query with a sub-select, something like

UPDATE products SET t=(SELECT f FROM products WHERE id=17) WHERE id=42;

or in case it's in the same row in a single table, just

UPDATE products SET t=f WHERE id=42;