CODEWITHSUNDEEP
mysqldate
user2657778
user2657778

Reputation: 105

Sorting a column in ascending and descending order depending on the current date

Please help. Here my mySQL query

SELECT * 
FROM (`products`)
ORDER BY (
CASE WHEN `ExpiredDate` > NOW() 
THEN 1 
ELSE 0 
END) DESC , `ExpiredDate` ASC

I'm going to sort the array result as the available products like this:

12-10-2013
12-15-2013
12-28-2013
12-09-2013 -- Today
12-08-2013
12-04-2013
12-01-2013

But the current result is now the expired products is order by ascending like this:

12-10-2013
12-15-2013
12-28-2013
12-09-2013 -- Today
12-01-2013
12-04-2013
12-08-2013

Upvotes: 1

Views: 110

Answers (2)

Chirag Parmar
Chirag Parmar

Reputation: 11

You should use following query instead of yours,

SELECT * FROM (products) ORDER BY ( CASE WHEN DATE_FORMAT(ExpiredDate,'%Y-%m-%d') > NOW() THEN 1 ELSE 0 END) DESC , ExpiredDate ASC

More details can be found at http://www.mysqltutorial.org/mysql-date_format/

Upvotes: 1

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

This should do the trick:

SELECT * FROM products
ORDER BY ExpiredDate < NOW(), ABS(TIMESTAMPDIFF(SECOND, expiredDate, NOW()))

Fiddle here

Upvotes: 1

Related Questions