how to convert a string to date in mysql?

mysql string date
Joseph Lafuente picture Joseph Lafuente · Mar 5, 2011 · Viewed 390.4k times · Source

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

bluefoot picture bluefoot · Mar 5, 2011

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. 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().

Related questions

How to use GROUP BY to concatenate strings in MySQL?

Basically the question is how to get from this: foo_id foo_name 1 A 1 B 2 C to this: foo_id foo_name 1 A B 2 C

Read More
How to escape single quotes in MySQL

How do I insert a value in MySQL that consist of single or double quotes. i.e This is Ashok's Pen. The single quote will create problems. There might be other escape characters. How do you insert the data properly?

Read More
MySQL - length() vs char_length()

What's the main difference between length() and char_length()? I believe it has something to do with binary and non-binary strings. Is there any practical reason to store strings as binary? mysql> select length('MySQL'), char_length('MySQL'); +-----------------+…

Read More