Mysql STR_TO_DATE 不正确的日期时间值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20219552/
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 STR_TO_DATE incorrect datetime value
提问by Margo
I've loaded some date from file to table and now i want to convert the string with date to a datetime format. The string i 'datestring' column looks like this '12-16-2010 01:48:28', and if i run this query:
我已经将一些日期从文件加载到表中，现在我想将带有日期的字符串转换为日期时间格式。字符串 i 'datestring' 列看起来像这个 '12-16-2010 01:48:28'，如果我运行这个查询：
select STR_TO_DATE('12-16-2010 01:48:28', '%c-%e-%Y %T')
It returns proper datetime: 2010-12-16 01:48:28
它返回正确的日期时间： 2010-12-16 01:48:28
But when i try to run this:
但是当我尝试运行它时：
update database.`temptable`
SET datetimefile = (SELECT STR_TO_DATE(datestring, '%c-%e-%Y %T'))
I get those kind of errors:
我收到这些错误：
Incorrect datetime value: ''12-16-2010 01:48:28'' for function str_to_date
Any ideas?
有任何想法吗？
采纳答案by Michael - sqlbot
Take a close look at the error message:
仔细看看错误信息：
Incorrect datetime value: ''12-16-2010 01:48:28''
^^ 2 single quotes ^^
Compare this to the normal error message:
将此与正常错误消息进行比较：
mysql> SELECT STR_TO_DATE('foo', '%c-%e-%Y %T');
+-----------------------------------+
| STR_TO_DATE('foo', '%c-%e-%Y %T') |
+-----------------------------------+
| NULL |
+-----------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+----------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: 'foo' for function str_to_date |
+---------+------+----------------------------------------------------------+
1 row in set (0.00 sec) ^ ^ just 1 single quote
Normally, the error message has a single set of single quotes. Yours has a double set, suggesting that you actually have a set of single quotes stored in your column data.
通常，错误消息有一组单引号。你的有一个双组，这表明你实际上在你的列数据中存储了一组单引号。
If this is the case, you can work around this by removing them where they exist:
如果是这种情况，您可以通过删除它们存在的位置来解决此问题：
SET datetimefile = (SELECT STR_TO_DATE(REPLACE(datestring,"'",''), '%c-%e-%Y %T'))
Using REPLACE()like this still would work even if not all of the rows contain the spurious quotes, since replace passes through the input value unchanged if the 'from_str' (2nd arg) doesn't occur.
REPLACE()即使并非所有行都包含虚假引号，这样使用仍然有效，因为如果 'from_str' （第二个参数）不发生，则替换通过输入值不变。
回答by Алексей Малаев
From PHP SQL Request Correct:
从 PHP SQL 请求正确：
LOAD DATA INFILE '.$filename."' INTO TABLE tablename (@var_DTime, `Product`, `Source`, `Cost`) SET `DTime` = str_to_date(@var_DTime,'%Y-%m-%dT%H:%i:%s')
Do not use: " " - 2010-12-31 01:48:28; - Don't work
不要使用：“” - 2010-12-31 01:48:28; - 不工作
Use "T" - 2010-12-31T01:48:28; - Work
使用“T” - 2010-12-31T01:48:28；- 工作
回答by Marc B
... SET datetimefile = STR_TO_DATE(datestring, '%c-%e-%Y-%T')
Note the lack of selectaround the str_to_date call. That select had no table reference, so the query failed with "unknown field datestring". That failure bubbled upwards and killed the entire overall query.
注意缺少selectstr_to_date 调用。该选择没有表引用，因此查询失败并显示“未知字段日期字符串”。该失败向上冒泡并杀死了整个查询。
