Order by date set with varchar type in MySQL

For this, use ORDER BY STR_TO_DATE in MySQL as in the below syntax −

select *from yourTableName ORDER BY STR_TO_DATE(yourColumnName, '%M %Y') DESC;

Let us first create a table −

mysql> create table DemoTable678(DueDate varchar(200)); Query OK, 0 rows affected (0.62 sec)

Insert some records in the table using insert command. We have inserted dates here −

mysql> insert into DemoTable678 values('March 2019'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable678 values('November 2018'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable678 values('January 2019'); Query OK, 1 row affected (0.18 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable678;

This will produce the following output −

+---------------+ | DueDate       | +---------------+ | March 2019    | | November 2018 | | January 2019 | +---------------+ 3 rows in set (0.00 sec)

Following is the query to order by date set with varchar type −

mysql> select *from DemoTable678 ORDER BY STR_TO_DATE(DueDate, '%M %Y') DESC;

This will produce the following output −

+---------------+ | DueDate       | +---------------+ | March 2019    | | January 2019 | | November 2018 | +---------------+ 3 rows in set (0.00 sec)