MySQL str_to_date produces NULL despite valid formatting

Question

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.

You are not ordering your data. Are you sure these are the same 2 records? — juergen d, Dec 03 '13 at 16:47
@juergend I have added the unique ID in the output now so that we can be sure that we are indeed looking at the same lines. — Dr. Mike, Dec 03 '13 at 16:56

score 3 · Accepted Answer · answered Dec 03 '13 at 16:52

3

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;

sqlfiddle demo

answered Dec 03 '13 at 16:52

Filipe Silva

21,189
5
53
68

Yes! That's it! It works. I cannot believe I missed that. Thank you. – Dr. Mike Dec 03 '13 at 16:57
:) It's the little things that are the most frustrating. ehehe. – Filipe Silva Dec 03 '13 at 16:58

MySQL str_to_date produces NULL despite valid formatting

1 Answers1

Linked