How to convert a string to date in mysql?

Question

I am having a string column which acts as a date and I need to select it as a date.

Will it be possible?

My sample data would be in this format, month/day/year -> 12/31/2011

Answer 1

You can try the following query:

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

You can likewise manage these date strings in WHERE clauses. For example,

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

You can manage all kinds of date/time layouts this way. Kindly refer to the format specifiers for the DATE_FORMAT() function to view what you can use into the second parameter of STR_TO_DATE().

If you are a beginner and want to learn SQL, then do check out the SQL Tutorial that will help you out in a better way.