MySQL：将日期时间插入其他日期时间字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8551940/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: Insert datetime into other datetime field
提问by marimba
I have a table with a DATETIME column. I would like to SELECT this datetime value and INSERT it into another column.
我有一个带有 DATETIME 列的表。我想选择此日期时间值并将其插入另一列。
I did this (note: '2011-12-18 13:17:17' is the value the former SELECT gave me from the DATETIME field):
我这样做了（注意：'2011-12-18 13:17:17' 是前 SELECT 从 DATETIME 字段中给我的值）：
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 doI put in there? All I want is reliably transfer the existing value over to a new datetime field...
好的，我知道在其中放置一个不带引号的字符串是错误的，但是 DATETIME 最初只是一个字符串吗？什么做我摆在那里？我想要的只是将现有值可靠地转移到新的日期时间字段...
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.
我问的原因是：我有一个特殊的定义，DATETIME，而且我认为它在处理日期时给了我一些安全性和其他优势。现在看来它只是一个专门的 VARCHAR，可以这么说。
Thanks for your answers, it seems this is indeed the intended behaviour.
感谢您的回答，这似乎确实是预期的行为。
回答by Mike Nakis
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
根据 MySQL 文档，您应该能够将该日期时间字符串括在单引号 ('YYYY-MM-DD HH:MM:SS') 中，它应该可以工作。看这里：日期和时间文字
So, in your case, the command should be as follows:
因此，在您的情况下，命令应如下所示：
UPDATE products SET former_date='2011-12-18 13:17:17' WHERE id=1
回答by Atonewell
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.
或者，您可能希望查看使用 STR_TO_DATE（请参阅STR_TO_DATE(str,format)）函数。
回答by GKV
for MYSQL try this
对于 MYSQL 试试这个
INSERT INTO table1(
myDatetimeField)VALUES(STR_TO_DATE('12-01-2014 00:00:00','%m-%d-%Y %H:%i:%s');
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
select * from table1
output- datetime= 2014-12-01 00:00:00
回答by Stefan L
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
如果您在其余代码中不需要 DATETIME 值，那么使用带有子选择的 UPDATE 查询会更高效、简单和安全，例如
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;
