mysql date ad,尽管有效的格式化，MySQL str_to_date仍会生成NULL

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" |

+--------------+

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 |

+-------------------------------+

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?

UPDATE: In response to comment

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 |

+----------+-------------------------------+

and

mysql> select AdId, Date from sampleData limit 2;

+----------+--------------+

| AdId | Date |

+----------+--------------+

| 84065013 | "25-01-2012" |

| 84206047 | "25-01-2012" |

+----------+--------------+

which shows that it's the same lines (ID) that are being processed.

解决方案

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

Try doing:

select str_to_date(REPLACE(Date,'"',''), '%d-%m-%Y')

from tab1 sampleData 2;