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

tip order_by 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

leave a comment