MYSQL sort date by future dates first in ASC and old dates then in DESC

user_9526337 0
Viewed 72

I want to sort dates in MYSQL in a way such that, future dates will be sorted first by in ASC order and, then then old dates last in DESC order. Here is the query I used to do, but both date types (future and old) are sorted out in ASC order. How can I fix this?

SELECT id, end_date FROM employee 
    ORDER BY CASE WHEN DATE(date) > DATE(NOW())
        THEN 0 
        ELSE 1 END, date ASC
case date sorting mysql
1 Answers

First, sort by the boolean expression date <= CURRENT_DATE so that all future dates are on the top and then sort by the absolute difference to the current date:

SELECT *
FROM employee 
ORDER BY date <= CURRENT_DATE,
         ABS(DATEDIFF(date, CURRENT_DATE));

See the demo.

user_104988280
Related