Date field not sorted 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 

Please note that dates are in October, November, September. This is my sql expression:

 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 are my dates not sorting correctly?

Click here to see it in action .

Answer 1

Now it is sorted by text on the result of DATE_FORMAT(cruise_date, '%c/%e/%Y') , and not on the cruise_date field. Try ORDER BY cruise.cruise_date .

Answer 2

This is because you are using the same alias in the SELECT . You need to change the alias to something else or use cruise.cruise_date in ORDER BY , otherwise the entries will be sorted in the text column from select. Try the following:

 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

Answer 3

Alternatively try arranging it for days_till_cruise

 ORDER BY `days_till_cruise`