MySQL STR_TO_DATE日期时间值不正确 [英] Mysql STR_TO_DATE incorrect datetime value

问题描述

我已经从文件到表中加载了一些日期，现在我想将带日期的字符串转换为日期时间格式。
日期字符串列中的字符串类似于 12-16-2010 01:48:28，并且如果我运行此查询：

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:

select STR_TO_DATE('12-16-2010 01:48:28', '%c-%e-%Y %T')

返回正确的日期时间： 2010-12-16 01:48:28

It returns proper datetime: 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'))

我遇到这类错误：

Incorrect datetime value: ''12-16-2010 01:48:28'' for function str_to_date

有任何想法吗？

推荐答案

仔细查看错误消息：

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')) 

使用 REPLACE（） 即使不是所有行都包含虚假引号，这仍然会起作用，因为如果未出现 from_str（第二个arg），则replace会不变地通过输入值。

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.

这篇关于MySQL STR_TO_DATE日期时间值不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！