mysqlstringdate

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

Solution

  • 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 DAY

    You can handle all kinds of date/time layouts this way:

    E.g 2011-12-31 would be possible by using %Y-%m-%d. Please refer to the format specifiers for the DATE_FORMAT() function to see what else you can put into the second parameter of STR_TO_DATE().