I have a column which holds values in the format 'June 2015', 'July 2015', 'March 2014', 'February 2016'.
I need to convert these into the format 06/2015, 07/2015, 03/2014, 02/2016.. but also make them a date so I can sort by this column.
I currently have the following select:
SELECT CONCAT(MONTH(STR_TO_DATE(LEFT(SUBSTRING_INDEX(quotation.quotation_exp_conversion_date, ' ', 1),3), '%b')), '/', SUBSTRING_INDEX(quotation.quotation_exp_conversion_date, ' ', -1)) AS exp_conversion_date FROM quotation WHERE quotation.quotation_status = 'LIVE' ORDER BY exp_conversion_date ASC
There are 2 problems. This query converts the values into the format '6/2015', '7/2015', '3/2014', '2/2016' (without the leading 0 for single digit months), and does not order by correctly as the YEAR is not taken into account - so they are ordered: '2/2016', '3/2014', '6/2015', '7/2015'
I tried wrapping it all in the
DATE() function but that just returned
NULL for all values.
Any ideas on how to achieve what I want?