CODEWITHSUNDEEP
mysql
Boardy
Boardy

Reputation: 36205

Date_Format and sort by date

I am currently experiencing a problem with sorting a result by a data column which is using date_format.

I have the below dates:

When I perform the query:

SELECT date FROM myTable ORDER date DESC

The dates are ordered in the correct order

When I perform the query

SELECT DATE_FORMAT(date, '%d-%m-%Y') as `date` ORDER BY date

The dates are now in the wrong order

I've also tried running the query SELECT DATE_FORMAT(date, '%d-%m-%Y') as date ORDER BY DATE_FORMAT(date, '%d-%m-%Y') but has made no difference.

How can I get this to sort in the correct order.

Upvotes: 6

Views: 10585

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

The problem is that you are overriding the column name with the alias.

Choose another alias:

SELECT DATE_FORMAT(`date`, '%d-%m-%Y') as date_formatted
FROM myTable 
ORDER BY `date` DESC

Upvotes: 12

zerkms
zerkms

Reputation: 254916

Just specify table name for the column in ORDER BY clause:

  SELECT DATE_FORMAT(date, '%d-%m-%Y') as `date`
    FROM myTable
ORDER BY myTable.`date` DESC -- <<<<<<

In this case mysql knows you want to sort by table column, not by the expression you've evaluated in the SELECT part

Upvotes: 6

Related Questions