Date field not sorting correctly in sql

Question

Here are my search results (by cruise_date):

CRUISE_DATE     DAYS_TILL_CRUISE    NAME
10/13/2012  29  Octobertfest
10/20/2012  36  Rare Air Show,
10/20/2012  36  Bugs and Bratz
11/10/2012  57  Fall Color Super Cruise
11/10/2012  57  Club Cruise-In to Desoto State Park
9/22/2012   8   Bugs on the Bayou
9/23/2012   9   Hot Dogs and Hot Rods

Notice that the dates go October, November, September. This is my sql statement:

SELECT
         DATE_FORMAT(cruise_date, '%c/%e/%Y') AS cruise_date,
         DATEDIFF(cruise_date, CURDATE()) AS days_till_cruise,
         NAME
FROM
         `cruise`
WHERE
         `cruise_date` >= '2012-09-14'
ORDER BY

       `cruise_date`

Why won't my dates sort correctly?

Click here to see this in action.

Answer 1

It now sorts textually on the result of DATE_FORMAT(cruise_date, '%c/%e/%Y'), not on the field cruise_date. Try ORDER BY cruise.cruise_date.

Answer 2

This is because you are using same alias in SELECT clause. You need to change alias to something different or use cruise.cruise_date in ORDER BY clause, otherwise records would get sorted on text column from select. Try this:

SELECT
         DATE_FORMAT(cruise_date, '%c/%e/%Y') AS cruise_date_1,
         DATEDIFF(cruise_date, CURDATE()) AS days_till_cruise,
         NAME
FROM
         `cruise`
WHERE
         `cruise_date` >= '2012-09-14'
ORDER BY

       `cruise_date`

SQLFiddle Demo