This question already has an answer here:
I have a sql table with a column named reference_number.
reference_number has values with a format of x-MMYY-xxxx. (MM is for month while YY is for year. -xxxx are auto-increment.)
My issue is that if I enter the following values into reference_number
Mar 2012 (x-0312-xxxx)
Apr 2012 (x-0412-xxxx)
Jan 2013 (x-0113-xxxx)
ORDER BY reference_number would sort them as
x-0113-xxxx
x-0312-xxxx
x-0412-xxxx
Is there a way to sort reference_number by the correct order of date.
</div>
I'm making the assumption that your reference_number column's data type is a character field?
Sorting by a character field will "do its best" to understand and interpret the data into a format by which it want sort, in this instance you'll note that it's interpreted the date part of your reference as a numerical value and as such, will sort them this way.
Alphanumeric data will be sorted as such.
If you require the data sorted by date, there are a number of solutions you can employ but ultimately you will need a DateTime value to sort by.
Either add a reference_date column and add the date values to this, either by direct insert or a DB trigger etc.
IMHO, you must have date values to be able to sort by date, however you decide to achieve that.