MySQL DATE_FORMAT function and ORDER BY

Category:

Tutorials > Database Management Systems > MySQL > Tips and Tricks

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.

Did this tutorial help a little? How about buy me a cup of coffee?

Buy me a coffee at ko-fi.com

Please feel free to use the comments form below if you have any questions or need more explanation on anything. I do not guarantee a response.

IMPORTANT: You must thoroughy test any instructions on a production-like test environment first before trying anything on production systems. And, make sure it is tested for security, privacy, and safety. See our terms here.

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 g 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 G 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 W on Oct 25, 2014

leave a comment on tutorial leave a comment