How to convert string to date in mysql?

Question

I have a string column that acts like date , and I want to select it as date .

Is it possible?

My example would be a data format; month/day/year → 12/31/2011

Answer 1

As MySQL said here Using a string column with date text as a date field , you can do

 SELECT STR_TO_DATE(yourdatefield, '%m/%d/%Y') FROM yourtable 

You can also handle these date strings in WHERE clauses. for example

 SELECT whatever FROM yourtable WHERE STR_TO_DATE(yourdatefield, '%m/%d/%Y') > CURDATE() - INTERVAL 7 DAYS 

This way you can handle all kinds of date and time schedules. Refer to the format specifiers for the DATE_FORMAT() function to see what you can put in the second parameter in STR_TO_DATE()

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Answer 2

 STR_TO_DATE('12/31/2011', '%m/%d/%Y') 

Answer 3

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
use the above page to reference other functions in MySQL

 SELECT STR_TO_DATE(StringColumn, '%d-%b-%y') FROM table 

let's say for example use the following query to get output

 SELECT STR_TO_DATE('23-feb-14', '%d-%b-%y') FROM table 

For String format use the link below

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Answer 4

Here are two more examples.

To display the day, month and year, you can use:

 select STR_TO_DATE('14/02/2015', '%d/%m/%Y'); 

What produces:

2015-02-14

To also display time, you can use:

 select STR_TO_DATE('14/02/2017 23:38:12', '%d/%m/%Y %T'); 

What produces:

2017-02-14 23:38:12