Search code examples
phpmysqlsql-order-bydate-sorting

How to sort morth and year values in the same field from mysql table using php

I have a mysql table with month and year in same field as below.

+----------------+
|    duedate     |
+----------------+
|   Sept '12     | 
|   Oct '12      | 
|   Nov '12      | 
|   May'13       | 
|   Mar'13       | 
|   Mar '13      | 
|   Jan '13      | 
|   Feb '13      | 
|   Dec '12      | 
|   Aug '12      | 
|   Apr '13      | 
+----------------+

I want to retrieve this table by sorting month and year values in DESC order as below. 

+------------+
|  duedate   |
+------------+
|   May'13   | 
|   Apr '13  | 
|   Mar '13  | 
|   Mar'13   | 
|   Feb '13  | 
|   Jan '13  | 
|   Dec '12  | 
|   Nov '12  |
|   Oct '12  |
|   Sept '12 |
|   Aug '12  | 
+------------+

Is it possible to sort as above.. I have tried below sql query but it sorts the data by DESC of year values only, month sorting is not working..

"SELECT DISTINCT duedate FROM sample_table ORDER BY substr(duedate, -2) DESC, FIELD(duedate, 'Dec', 'Nov', 'Oct', 'Sep', 'Aug', 'Jul', 'Jun', 'May', 'Apr', 'Mar', 'Feb', 'Jan')"

Please help..

Solution

  • you can try like this-

    SELECT STR_TO_DATE(duedate,'%M \'%Y') as due  FROM demo ORDER BY due DESC

    I am not sure but try with str_to_date in mysql

    See dem link: SQLFIDDLE