+-
我正在尝试从 mysql使用str_to_date函数将TEXT字段转换为日期.现在正在运行：
mysql> select Date from sampleData limit 2;
+--------------+
| Date |
+--------------+
| "25-01-2012" |
| "25-01-2012" |
+--------------+
表明我们确实有文字.只是为了验证这是describe表的输出：
mysql> describe sampleData;
+--------------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------+------+-----+---------+-------+
| Colors | tinyint(4) | YES | | NULL | |
| Date | text | NO | | NULL | |
+--------------------+------------+------+-----+---------+-------+
现在,当我尝试将其转换为日期时,变得有趣了：
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,因此我进行了强制转换,产生了相同的结果.我究竟做错了什么？
更新：回应评论
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" |
+----------+--------------+
这表明正在处理的是相同的行(ID).
最佳答案
看来您也有那些双引号.当您尝试使用双引号将其转换时,您将得到null.
尝试做：
select str_to_date(REPLACE(Date,'"',''), '%d-%m-%Y')
from tab1 sampleData 2;
sqlfiddle demo
点击查看更多相关文章
转载注明原文：尽管有效格式化,MySQL str_to_date仍会产生NULL - 乐贴网
