Converting a date in MySQL from string field

Question

I'm using a system where the dates are stored as strings in the format dd/mm/yyyy. Is it possible to convert this to yyyy-mm-dd in a SELECT query (so that I can use DATE_FORMAT on it)? Does MySQL have a date parsing function?

Currently the only method I can think of is to concatenate a bunch of substrings, but hopefully there's a simpler solution.

(Unfortunately I can't convert the field to a true date field since it's a meta-table: the same column contains values for different fields that are just strings.)

Answer 1

This:

STR_TO_DATE(t.datestring, '%d/%m/%Y') 

...will convert the string into a datetime datatype. To be sure that it comes out in the format you desire, use DATE_FORMAT:

DATE_FORMAT(STR_TO_DATE(t.datestring, '%d/%m/%Y'), '%Y-%m-%d') 

If you can't change the datatype on the original column, I suggest creating a view that uses the STR_TO_DATE call to convert the string to a DateTime data type.

Answer 2

Yes, there's str_to_date

mysql> select str_to_date("03/02/2009","%d/%m/%Y"); +--------------------------------------+ | str_to_date("03/02/2009","%d/%m/%Y") | +--------------------------------------+ | 2009-02-03                           | +--------------------------------------+ 1 row in set (0.00 sec)