STR_TO_DATE() function of MySQL

Question

I have date in this format 5 Mar 1985 0:00 stored in table as VARCHAR.
I want to convert it into Datetime, I am using the STR_TO_DATE() as follows:

SELECT STR_TO_DATE(birth_date, '%d %m %Y %h:%i') FROM student WHERE pk = 29

But it returns NULL.

You should be using MySQL's standard date format instead of your own custom one. If you did you wouldn't have to worry about this. — John Conde, Sep 07 '12 at 11:32
I actually have the values already stored in the table (birth_date varchar 100) I need to convert it into datetime. — user1369905, Sep 07 '12 at 11:36

score 5 · Answer 1 · answered Sep 07 '12 at 11:43

5

Try this format - '%e %b %Y %k:%i', for example -

SELECT STR_TO_DATE('5 Mar 1985 2:33', '%e %b %Y %k:%i') dt;
+---------------------+
| dt                  |
+---------------------+
| 1985-03-05 02:33:00 |
+---------------------+

answered Sep 07 '12 at 11:43

Devart

119,203
23
166
186

score 1 · Answer 2 · answered Sep 07 '12 at 11:32

1

Try STR_TO_DATE(birth_date, '%d %M %Y %h:%i') instead

%m = month in integer %M = month in string(ex: Mar)

answered Sep 07 '12 at 11:32

Cosmin

1,482
12
26

Maybe because the date is in an invalid format? It should have been '5 Mar 1985 00:00'. Try : `STR_TO_DATE(birth_date, '%d %M %Y 0%h:%i')` – Cosmin Sep 07 '12 at 11:39
Found this one working STR_TO_DATE(birth_date, '%d %b %Y %i:%s') – user1369905 Sep 07 '12 at 11:45

STR_TO_DATE() function of MySQL

2 Answers2