MySQL query to order and display difference between dates from the current date

For this, use ORDER BY clause. The current date is as follows −

mysql> select now(); +---------------------+ | now() | +---------------------+ | 2019-06-09 21:08:16 | +---------------------+ 1 row in set (0.00 sec)

Let us first create a table −

mysql> create table DemoTable    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> DueDate datetime    -> ); Query OK, 0 rows affected (0.62 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable(DueDate) values('2019-06-12'); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable(DueDate) values('2019-06-01'); Query OK, 1 row affected (0.37 sec) mysql> insert into DemoTable(DueDate) values('2019-06-05'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(DueDate) values('2019-06-10'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(DueDate) values('2019-06-11'); Query OK, 1 row affected (0.66 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

Output

+----+---------------------+ | Id | DueDate | +----+---------------------+ | 1 | 2019-06-12 00:00:00 | | 2 | 2019-06-01 00:00:00 | | 3 | 2019-06-05 00:00:00 | | 4 | 2019-06-10 00:00:00 | | 5 | 2019-06-11 00:00:00 | +----+---------------------+ 5 rows in set (0.00 sec)

Here is the query to order by to get the difference and order the dates. The difference between the dates are displayed in a separate column −

mysql> select Id,DueDate,DATEDIFF(DueDate, CURDATE()) AS t from DemoTable -> order by CASE WHEN t < 0 THEN 1 ELSE 0 END, t;

Output

+----+---------------------+------+ | Id | DueDate | t | +----+---------------------+------+ | 4 | 2019-06-10 00:00:00 | 1 | | 5 | 2019-06-11 00:00:00 | 2 | | 1 | 2019-06-12 00:00:00 | 3 | | 2 | 2019-06-01 00:00:00 | -8 | | 3 | 2019-06-05 00:00:00 | -4 | +----+---------------------+------+ 5 rows in set (0.00 sec)