how to convert a string to date in mysql?

Question

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

Answer 1

Here's another two examples.

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

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

Which produces:

2015-02-14

To also output the time, you can use:

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

Which produces:

2017-02-14 23:38:12

Answer 2

As was told at MySQL 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

You can handle all kinds of date/time layouts this way. Please refer to the format specifiers for the DATE_FORMAT() function to see what you can put into the second parameter of STR_TO_DATE().

Answer 3

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

Answer 4

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

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

say for example use the below query to get output

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

For String format use the below link

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