Ignore characters in date string when using str_to_date

Question

I'm importing data from an XML file where the date field is stored as text. Normally this would be easy to convert within mysql, however the problem is that the dates contain the %e element stored as 1st, 2nd, 3rd, 4th, etc.. As such it makes it impossible to apply a standard consistent format string.

An example of the data in the date field is as follows:

Monday 25th August 2014

Saturday 1st November 2014

My query is as follows:

SELECT STR_TO_DATE(`date`, '%W %e %M %Y') as 'new_date' FROM `temp_match`;

Due to the additional characters in the date string this fails. So I adjusted my format string to '%W %eth %M %Y' and as expected this only works for the dates with 'th' in them. I cannot workout how to exclude the characters for 'st' and 'nd' which can occur in the dates.

Answer 1

This method works but is not ideal. The trick is to replace '1st', '2nd', '3rd' with '1th', '2th' and '3th' to allow str_to_date to work its magic.

First create a FUNCTION which will clean up the issues outlined above and then perform the conversion to DATE

BEGIN
    DECLARE new_date DATE;
    SET old_date = REPLACE(old_date, "1st", "1th");
    SET old_date = REPLACE(old_date, "2nd", "2th");
    SET old_date = REPLACE(old_date, "3rd", "3th");
    SET new_date = STR_TO_DATE(`old_date`, '%W %eth%M %Y');
    RETURN new_date;
END