CODEWITHSUNDEEP
mysql
Ahmed
Ahmed

Reputation: 365

sorting - Order By date ASC

I have "date" column in mysql saving the dates with this format
17-09-2014 (DD-MM-YYYY)

I need to sort them ascending, so I used this command: 

SELECT * FROM table ORDER BY date ASC

But I found this result: 

17-09-2014
18-09-2015
19-09-2014

It should be: 

17-09-2014
19-09-2014
18-09-2015

It sorts the day only ASC not the full date

Upvotes: 6

Views: 21334

Answers (2)

munsifali
munsifali

Reputation: 1732

You can use STR_TO_DATE() to convert your strings to MySQL date values and ORDER BY the result:

ORDER BY STR_TO_DATE(datestring, '%d/%m/%Y')

However, you would be wise to convert the column to the DATE data type instead of using strings

Upvotes: 3

Alex
Alex

Reputation: 11579

Try this:

SELECT * FROM table ORDER BY STR_TO_DATE(date,'%d-%m-%Y') ASC

Upvotes: 7

Related Questions