CODEWITHSUNDEEP
mysql
GelS Sy
GelS Sy

Reputation: 41

How can I sort date perfectly

I was trying to sort the data in-order by date but it doesn't sort perfectly. I don't know if there's an error in the code.

Here's some of the data in character in my database:

March 5, 2016
March 17, 2016
August 9, 2017
April 2, 2016
July 5,2018
January 15, 2019

The query I used is:

SELECT * FROM reporting  ORDER BY date ASC

Result are:

April 2, 2016
August 9, 2017
January 15, 2019
July 5,2018
March 5, 2016
March 17, 2016

Expected Result:

March 5, 2016
March 17, 2016
April 2, 2016
August 9, 2017
July 5,2018
January 15, 2019

Upvotes: 2

Views: 84

Answers (3)

DarkRob
DarkRob

Reputation: 3833

You may try this,

For SQL Server:

convert( varchar, [date], 120) will convert your date in yyyy-MM-dd format.

 select * from reporting order by convert( varchar, [date], 120) asc

For MySQL:

 select * from reporting order by DATE_FORMAT(date, "%Y-%m-%e") asc

Upvotes: 0

PHP Geek
PHP Geek

Reputation: 4033

use date format

ORDER BY DATE_FORMAT(date, "%F-%d-%Y") as date ASC

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

use str_to_date() to convert your date field from string to datetime

SELECT * FROM reporting  ORDER BY str_to_date(`date`,'%M %d, %Y') ASC

Upvotes: 3

Related Questions