CODEWITHSUNDEEP
mysqlsql-order-by
Garrett
Garrett

Reputation: 11725

MySQL: ORDER BY with empty date '0000-00-00' as last but the rest ASC

In our database, instead of making empty dates NULL, they are '0000-00-00' (I know, it sucks). How can I order these so that dates that are not '0000-00-00' are first and ordered ASC, and then the empty dates of '0000-00-00' come after?

Thanks!

Upvotes: 5

Views: 3745

Answers (4)

user319198
user319198

Reputation:

Try Below :

  SELECT * FROM your_table ORDER BY (date_column='0000-00-00'), date_column ASC

OR

select * from your_table
order by if(date_column='0000-00-00',1,0),date_column;

Upvotes: 5

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115620

ORDER BY (DateColumn = 0)
       , DateColumn

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135858

...
ORDER BY CASE WHEN YourDateColumn = '0000-00-00' THEN 2 ELSE 1 END,
         YourDateColumn

Upvotes: 11

Ricardo Souza
Ricardo Souza

Reputation: 16466

You can use a CASE WHEN http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

... ORDER BY (CASE WHEN date = '0000-00-00' THEN 1 ELSE 0 END) ASC, otherColumns asc, ...

Upvotes: 2

Related Questions