MySQL sort on year/month/day

问题:

I have a large list of dates of the format dd-mm-yyyy. So I want to order on: year, then on month and then on day.

Date and month are in the same field and year is an other field.

I have now: ORDER BY table.year ASC, table.date ASC

The result is that the list is order on year and then days.
How to split/strip the dd-mm format and first sort on month before sorting on days?

Same record:    
date | year   
dd-mm  | yyyy

回答1:

based on your example you can sort the record like this,

ORDER BY STR_TO_DATE(CONCAT(year, '-', date), '%Y-%d-%m') ASC

• SQLFiddle Demo

回答2:

As per my knowledge it's better to use single date type field instead of having seperate two fields for date-month and year as you can easily sort your results.

According to your query date-month can be stripped out using RIGHT(date-monthfield, 2) function. This selects month which is on the right side.

The query would be:

select RIGHT(date-monthfield, 2) from table ORDER BY date-monthfield ASC;

Hope it helps.