CODEWITHSUNDEEP
phpdatemysql
atMaarten
atMaarten

Reputation: 93

SQL Sort on date (D-M)

I have a field in my database as text, which contain a D-M date value. The year is a new field. So ORDER BY year ASC is not that difficult. Now I wanted to also ORDER BY date. Only problem is, ORDER BY year ASC, ORDER BY date does not work. I think because of the text field. Is it still possible to sort them on year, month, day ASC?

Thanks!

Upvotes: 1

Views: 94

Answers (1)

William Jackson
William Jackson

Reputation: 1165

Use SUBSTRING_INDEX():

... ORDER BY year ASC, SUBSTRING_INDEX(date, '-', -1) ASC, SUBSTRING_INDEX(date, '-', 1) ASC
  • SUBSTRING(date, '-', -1) will give you everything after the - (the month).
  • SUBSTRING(date, '-', 1) will give you everything before the - (the day).

Upvotes: 2

Related Questions