MySQL DATE_FORMAT function and ORDER BY

An interesting scenario that I came across today was the fact that if you use the same alias as the column name in a formatted string (using DATE_FORMAT) and you also use same alias in ORDER BY or GROUP BY clause then MySQL uses the string output of DATE_FORMAT to sort records rather than the actual date column. Let me clarify using an example. Suppose you have the following table definition:

CREATE TABLE test (id INT, date_added DATE);

with the following records:

+------+------------+
| id   | date_added |
+------+------------+
|    1 | 2011-08-01 |
|    2 | 2010-12-01 |
+------+------------+

And the following query results in below:

mysql> SELECT id, date_added FROM test ORDER BY date_added DESC;
+------+------------+
| id   | date_added |
+------+------------+
|    1 | 2011-08-01 |
|    2 | 2010-12-01 |
+------+------------+
2 rows in set (0.00 sec)

But, if you format date column and run query below then you will be surprised to see the order of rows.

mysql> SELECT id, DATE_FORMAT(date_added, '%e %b, %Y') AS date_added FROM test ORDER BY date_added DESC;
+------+-------------+
| id   | date_added  |
+------+-------------+
|    2 | 1 Dec, 2010 |
|    1 | 1 Aug, 2011 |
+------+-------------+
2 rows in set (0.00 sec)

The reason why this happens is because the alias for DATE_FORMAT formatted string is the same as column name, so MySQL chooses the alias to sort records for the resultset. An easy fix is to choose a different alias name for DATE_FORMAT string.

mysql> SELECT id, DATE_FORMAT(date_added, '%e %b, %Y') AS date_added_formatted FROM test ORDER BY date_added DESC;
+------+----------------------+
| id   | date_added_formatted |
+------+----------------------+
|    1 | 1 Aug, 2011          |
|    2 | 1 Dec, 2010          |
+------+----------------------+
2 rows in set (0.00 sec)

As of MySQL 5.1.15, DATE_FORMAT() returns a string with a character set and collation given by character_set_connection and collation_connection so that it can return month and weekday names containing non-ASCII characters. Before 5.1.15, the return value is a binary string.

Please feel free to use the comments form below if you have any questions or need more explanation on anything. I recommend thoroughy testing on a production-like test system first before moving to production. Use the icons below to share this tutorial with your friends.

tags cloud

order_by tip date_format function mysql

popular searches

Comments (write a comment):

Another alternative is to fully qualify date_added in the order by clause:
SELECT id, DATE_FORMAT(date_added, '%e %b, %Y') AS date_added FROM test ORDER BY test.date_added DESC; Posted by: Anonymous on Dec 12, 2011

And when you've got UNION ALL ???? cause i got

Unknown column 'donnees.dates' in 'order clause' Posted by: Mickaeldp on Jan 31, 2012

Please paste the SQL here, you may be putting ORDER BY at the wrong place in your query. There is a little detail on how to use ORDER BY with UNION. Posted by: shahryar ghazi on Feb 01, 2012

it is quite helpful :) Posted by: nabila on Jan 07, 2013

The current functionality makes more sense, but as a policy you should avoid making your aliases the same as your field names. Posted by: Todd Grigsby on Nov 26, 2013

To make this easy, you could make a table view to make the query much more easier, and with regards to the naming convention, it's okay to use your field names as aliases, but if possible, refrain from reusing field names. Especially when they are queried from the same table. Posted by: Cindy Whitman on Oct 25, 2014

leave a comment