Q:

How to convert dates in "24-Jun-2013" format to MySQL?

I am trying to convert a large table that has dates in the format Day of Month - Month Abbreviation - Year in a row separated by dashes, like 01-Jan-2014.  The closest question I found is this one where the asker ended up doing a search and replace method instead.  I could do that too, but I think that surely there is a better way.  My code:
SELECT STR_TO_DATE(datecolumn, '%c/%d/%Y') FROM 'Table'

Returns null with dates in the above format but is fine with other date formats.  I also tried DATE_FUNCTION and other suggestions I saw online.

A:

This will do it:
SELECT 
    DATE_FORMAT(
      STR_TO_DATE(
        REPLACE(
          SUBSTRING_INDEX(datecolumn,'-',-2),
          '-',
          CONCAT(' ', SUBSTRING_INDEX(datecolumn, '-', 1), ', ')
        ),
        '%M %d, %Y'
      ),
      '%Y-%m-%d'
    )

FROM `Table`;

Explanation:

We use SUBSTRING_INDEX(datecolumn,'-',-2) to get the last two pieces, like Jan-2014
We replace the - in Jan-2014 with the first piece (using SUBSTRING_INDEX), i.e., 01, but also use CONCAT to add spaces and ,, so we get Jan 01, 2014
We use STR_TO_DATE and tell it the format is %M %d, %Y
We then put it in %Y-%m-%d format with DATE_FORMAT()

Examples:
07-Jan-2014  ->  2014-01-07
9-May-2001   ->  2001-05-09
13-Dec-1978  ->  1978-12-13

Here's a fiddle

