尽管有效的格式化，MySQL str_to_date仍会生成NULL [英] MySQL str_to_date produces NULL despite valid formatting

问题描述

我试图使用来自mysql的str_to_date函数将TEXT字段转换为日期。正在运行：

I'm trying to use the str_to_date function from mysql to convert a TEXT field to a date. Now running:

mysql> select Date from sampleData limit 2;
+--------------+
| Date         |
+--------------+
| "25-01-2012" |
| "25-01-2012" |
+--------------+

我们确实有文字。只是为了验证这里从describe表的输出：

shows that we indeed have text. And just to verify here's the output from describe table:

mysql> describe sampleData;
+--------------------+------------+------+-----+---------+-------+
| Field              | Type       | Null | Key | Default | Extra |
+--------------------+------------+------+-----+---------+-------+
| Colors             | tinyint(4) | YES  |     | NULL    |       |
| Date               | text       | NO   |     | NULL    |       |
+--------------------+------------+------+-----+---------+-------+

到有趣的部分，当我尝试转换为一个日期：

Now to the funny part when I try to convert this to a date:

mysql> select str_to_date(Date, '%d-%m-%Y') from sampleData limit 2;
+-------------------------------+
| str_to_date(Date, '%d-%m-%Y') |
+-------------------------------+
| NULL                          |
| NULL                          |
+-------------------------------+

但是str_to_date仅适用于字符类型，而不是TEXT我想，所以我做了一个转换，产生相同的结果。我做错了什么？

Oh but str_to_date only works with character types and not TEXT I thought so I did a cast which produces the same result. What am I doing wrong?

更新：回应评论

mysql> select AdId, str_to_date(Date, "%d-%m-%Y") from sampleData limit 2;
+----------+-------------------------------+
| AdId     | str_to_date(Date, "%d-%m-%Y") |
+----------+-------------------------------+
| 84065013 | NULL                          |
| 84206047 | NULL                          |
+----------+-------------------------------+

和

mysql> select AdId, Date from sampleData limit 2;
+----------+--------------+
| AdId     | Date         |
+----------+--------------+
| 84065013 | "25-01-2012" |
| 84206047 | "25-01-2012" |
+----------+--------------+

$

推荐答案

它看起来像你有那些双引号在那里也。

It looks like you have those doublequotes in there also. When you try to convert it with the doublequotes, you get null.

尝试执行以下操作：

select str_to_date(REPLACE(Date,'"',''), '%d-%m-%Y') 
from tab1 sampleData 2;

sqlfiddle demo

这篇关于尽管有效的格式化，MySQL str_to_date仍会生成NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！