MySQL Order By date type issue
This must be the “gotcha” type of stuff when you think just using the “ORDER BY <date type>” clause would give you back the correct results in MySQL.
My original table structure and its data:
mysql> describe holidays; +-----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+----------------+ | holidayId | int(11) | NO | PRI | NULL | auto_increment | | holidayName | varchar(255) | NO | | NULL | | | holidayDate | date | NO | | NULL | | | holidayDuration | int(11) | NO | | NULL | | +-----------------+--------------+------+-----+---------+----------------+
mysql> select holidayId, holidayName, holidayDate from holidays; +-----------+-------------------+-------------+ | holidayId | holidayName | holidayDate | +-----------+-------------------+-------------+ | 1 | Memorial Day | 2014-05-26 | | 2 | Independence Day | 2014-07-04 | | 3 | Labor Day | 2014-09-01 | | 4 | Thanks Giving Day | 2014-11-27 | | 5 | Christmas Day | 2014-12-25 | | 6 | New Year's Day | 2015-01-01 | +-----------+-------------------+-------------+
Then I’d query the table with the ORDER BY clause with some DATE_FORMAT():
mysql> SELECT holidayId, holidayName, date_format(holidayDate,'%m/%d/%Y') holidayDate -> FROM holidays -> ORDER BY holidayDate; +-----------+-------------------+-------------+ | holidayId | holidayName | holidayDate | +-----------+-------------------+-------------+ | 6 | New Year's Day | 01/01/2015 | | 1 | Memorial Day | 05/26/2014 | | 2 | Independence Day | 07/04/2014 | | 3 | Labor Day | 09/01/2014 | | 4 | Thanks Giving Day | 11/27/2014 | | 5 | Christmas Day | 12/25/2014 | +-----------+-------------------+-------------+
As you can see, the New Year’s Day for 2015 is at top. It seems like the records were sorted based on the numbers and not by the chronological orders.
According to the manual (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format), the DATE_FORMAT() would return “a string.”
Thus, the correct way to sort the date type column is to sort by the original column name. In our case, the original column name was aliased/renamed as “holidayDate.” If we did not alias this, it would have been:
mysql> SELECT date_format(holidayDate,'%m/%d/%Y')
-> FROM holidays;
+-------------------------------------+
| date_format(holidayDate,'%m/%d/%Y') |
+-------------------------------------+
| 05/26/2014 |
| 07/04/2014 |
| 09/01/2014 |
| 11/27/2014 |
| 12/25/2014 |
| 01/01/2015 |
+-------------------------------------+
===> it became "date_format(holidayDate,'%m/%d/%Y')"
Thus my workaround is (I want to use alias!):
mysql> SELECT holidayId, holidayName, date_format(holidayDate,'%m/%d/%Y') 'Holiday Date' -> FROM holidays -> ORDER BY holidayDate; +-----------+-------------------+--------------+ | holidayId | holidayName | Holiday Date | +-----------+-------------------+--------------+ | 1 | Memorial Day | 05/26/2014 | | 2 | Independence Day | 07/04/2014 | | 3 | Labor Day | 09/01/2014 | | 4 | Thanks Giving Day | 11/27/2014 | | 5 | Christmas Day | 12/25/2014 | | 6 | New Year's Day | 01/01/2015 | +-----------+-------------------+--------------+
HP 10BII financial calculator – comma and decimal point swap
This is really funny – one day, I realized that my HP© 10BII financial calculator is displaying a comma instead of a decimal point and displaying a decimal point for a comma…
Very confusing…
The fix is to press “shift”(brown key on the left) and “.” (the decimal point).
The calculator’s user manual is downloadable from the manufacturer’s site: direct link to the manual