STR_TO_DATE() function of MySQL

mysql date string-to-datetime
user1369905 picture user1369905 · Sep 7, 2012 · Viewed 18k times · Source

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.

Answer

Devart picture Devart · Sep 7, 2012

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

Related questions

MySQL Query GROUP BY day / month / year

Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a TIMESTAMP field, like: SELECT COUNT(id) FROM stats WHERE record_date.…

Read More
How to select date from datetime column?

I have a column of type "datetime" with values like 2009-10-20 10:00:00 I would like to extract date from datetime and write a query like: SELECT * FROM data WHERE datetime = '2009-10-20' ORDER BY datetime DESC Is the …

Read More
how to convert a string to date in mysql?

I have a string column which acts as a date and I want to select it as a date. Is it possible? My sample data format would be; month/day/year -> 12/31/2011

Read More