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

User · Answer

STR TO DATE allows you to do this  and it has a format argument

User · Answer

SELECT STR TO DATE dateString    d  m  y   FROM yourTable

User · Answer

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

User · Answer

Yes  there s str to date  mysql gt  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

[mysql] Converting a date in MySQL from string field

Examples related to mysql

Examples related to date