Dates not coming back in order....

My sql query will not seem to bring back dates in the correct order. It
will brin them back in a semi-order. Here is the query and what it
brings back. Any thoughts on how to get the dates to come back in
order, formatted the way I want? Thanks.


QUERY:

select num, DATE_FORMAT(startDate,'%b %e, %Y') as startDate,
DATE_FORMAT(endDate, '%b %e, %Y') as endDate, reason from
exceptionDates order by startDate DESC, endDate DESC;

RESULTS:

+-----+--------------+--------------+----------------------+
| num | startDate | endDate | reason |
+-----+--------------+--------------+----------------------+
| 2 | Sep 1, 2006 | Sep 4, 2006 | Labor Day |
| 3 | Oct 6, 2006 | Oct 10, 2006 | Fall Break |
| 5 | Nov 21, 2006 | Nov 26, 2006 | thanksgiving |
| 4 | Nov 10, 2006 | Nov 12, 2006 | veteran's day |
| 12 | Mar 5, 2007 | Mar 11, 2007 | Spring Break Week |
| 11 | Mar 2, 2007 | Mar 4, 2007 | Spring Break Weekend |
| 10 | Jan 12, 2007 | Jan 15, 2007 | MLK Weekend |
| 7 | Dec 30, 2006 | Jan 1, 2007 | new year's break |
| 6 | Dec 23, 2006 | Dec 29, 2006 | Christmas Break |
| 9 | Apr 14, 2007 | Apr 14, 2007 | Special Closing |
+-----+--------------+--------------+----------------------+

Re: Dates not coming back in order....

aquanutz@gmail.com wrote:
> My sql query will not seem to bring back dates in the correct order. It
> will brin them back in a semi-order. Here is the query and what it
> brings back. Any thoughts on how to get the dates to come back in
> order, formatted the way I want? Thanks.
>
>
> QUERY:
>
> select num, DATE_FORMAT(startDate,'%b %e, %Y') as startDate,
> DATE_FORMAT(endDate, '%b %e, %Y') as endDate, reason from
> exceptionDates order by startDate DESC, endDate DESC;
>
> RESULTS:
>
> +-----+--------------+--------------+----------------------+
> | num | startDate | endDate | reason |
> +-----+--------------+--------------+----------------------+
> | 2 | Sep 1, 2006 | Sep 4, 2006 | Labor Day |
> | 3 | Oct 6, 2006 | Oct 10, 2006 | Fall Break |
> | 5 | Nov 21, 2006 | Nov 26, 2006 | thanksgiving |
> | 4 | Nov 10, 2006 | Nov 12, 2006 | veteran's day |
> | 12 | Mar 5, 2007 | Mar 11, 2007 | Spring Break Week |
> | 11 | Mar 2, 2007 | Mar 4, 2007 | Spring Break Weekend |
> | 10 | Jan 12, 2007 | Jan 15, 2007 | MLK Weekend |
> | 7 | Dec 30, 2006 | Jan 1, 2007 | new year's break |
> | 6 | Dec 23, 2006 | Dec 29, 2006 | Christmas Break |
> | 9 | Apr 14, 2007 | Apr 14, 2007 | Special Closing |
> +-----+--------------+--------------+----------------------+
>
You have a name conflict. The 'startDate' is the database is NOT the
'startDate' which is used in the 'order by' clause, rather the one in
the 'select ... as' clause. You have two choices: either use a different
name in the 'select ... as' clause or use the fully qualified name
'exceptionDates.startDate' in the 'order by' clause.

Re: Dates not coming back in order....

aquanutz@gmail.com schreef:
> My sql query will not seem to bring back dates in the correct order. It
> will brin them back in a semi-order. Here is the query and what it
> brings back. Any thoughts on how to get the dates to come back in
> order, formatted the way I want? Thanks.

Hi Aquanutz,

Your query does exactly what you ask it to. I.e. it sorts your results
by your aliased formatted dates, hence you first get september (starting
with an 's') then october (starting with an 'o') etc.
I assume you want to sort by date (i.e. the original startdate in date
format), so you should just rename your aliases startDate and endDate
into something else and then sort by startDate and endDate (the original
columns).

HTH.
Peter.
--
http://www.phpforums.nl