MySQL STR_TO_DATE函数日期时间值错误

Question

我已经从文件中加载了一些数据到表格中，现在我想将日期字符串转换为日期时间格式。'datestring'列中的字符串看起来像这样'12-16-2010 01:48:28'，如果我运行以下查询：

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

它返回适当的日期时间：2010-12-16 01:48:28

但是当我尝试运行这个时：

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

有什么想法吗？

- Margo

---

请查看我的答案这里。 - mixel

Answer 1

请仔细查看错误信息：

Incorrect datetime value: ''12-16-2010 01:48:28''
                          ^^  2 single quotes  ^^

将其与普通的错误消息进行比较：

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 

通常，错误消息只有一组单引号。而你的错误消息中有两组单引号，这表明你的列数据实际上包含了一组单引号。

如果是这种情况，你可以通过删除存在的单引号来解决问题：

SET datetimefile = (SELECT STR_TO_DATE(REPLACE(datestring,"'",''), '%c-%e-%Y %T')) 

即使不是所有行都包含错误引号，使用像这样的REPLACE()仍然可以工作，因为如果“from_str”（第二个参数）不存在，则replace会将输入值保持不变。

- Michael - sqlbot

---

搞定了 :) 謝謝 - Margo

Answer 2

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

---

不要使用: " " - 2010年12月31日01:48:28; - 不起作用

---

使用"T" - 2010年12月31日T01:48:28; - 起作用

- Алексей Малаев

---

这里的空格运作良好。此外，如果STR_TO_DATE()不能符合可能是非常大的输入文件的输入格式，它将不会非常有用。2010-12-31T01:48:28是ISO 8601格式，但您不能期望随机外部输入符合该格式。 - ebyrob

Answer 3

当您尝试在DateTime列中添加一个非DateTime值时，会发生此错误。

例如，DateTime格式为YYYY-MM-DD HH:MM:SS。因此，在任何情况下，如果您尝试添加任何格式的日期而不是原始格式，则会引发此错误。

在我的情况下，我正在使用LOAD DATA INFILE将CSV文件加载到特定表中。 CSV文件中的一列采用另一种日期格式，因此我应该使用以下语法使用str_to_date函数更改格式：

LOAD DATA INFILE 'x.csv'
INTO TABLE orders FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(
     timimg,
     order_id,
     order_details
)
set timing= STR_TO_DATE(lower(@timing), '%d-%b-%Y %H:%i:%s');

上述语法会抛出相同的错误（函数str_to_date的时间值不正确），那么问题是什么？

问题只在于为timing列添加@字符。@字符表示您告诉MySQL不要直接将数据加载到该列的表中，而是在将其插入表之前对CSV中的值进行一些更改。

通过在上述查询中添加@字符，可以解决该问题。正确的语法是：

LOAD DATA INFILE 'x.csv'
INTO TABLE orders FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(
      @timimg,
      order_id,
      order_details
)
set timing= STR_TO_DATE(lower(@timing), '%d-%b-%Y %H:%i:%s');

- ASammour

Answer 4

我曾经遇到过类似的问题， 我想使用按日期排序的语法，但由于我的日期是以文本格式存储的，所以返回的表格没有排序。 我尝试使用

Alter table 
 Alter column `column name` date

但它给了我和你一样的错误。

我通过试错找到的唯一解决方案是更改CSV文件中日期的格式。我将其更改为YYYY-MM-DD，然后使用“Alter table，alter column column name”更改列中的数据类型。

- Arsalan Naeimipoor

Answer 5

... SET datetimefile = STR_TO_DATE(datestring, '%c-%e-%Y-%T')

请注意str_to_date调用周围缺少select。那个select没有表引用，因此查询失败并显示“未知字段datestring”。该失败向上冒泡并杀死了整个查询。

- Marc B