SEO

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 |
+-----+--------------+--------------+----------------------+

On 25 Sep 2006 09:11:25 -0700, aquanutz@gmail.com wrote:

>select num, DATE_FORMAT(startDate,'%b %e, %Y') as startDate
^^^^^^^^^ ^^^^^^^^^

Are you sure you want to do this?

It looks like the query is doing exactly what you told it to do.

Maybe try:

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

I haven't tested it ... let us know if it works.

--

Dennis K.

aquanutz@gmail.com wrote:

> My sql query will not seem to bring back dates in the correct order.

> 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;
....

> +-----+--------------+--------------+----------------------+
>| 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 |
> +-----+--------------+--------------+----------------------+

That's exactly what you asked for. DATE_FORMAT() delivers a string,
strings are sorted lexicographically. If you want chronological order,
just sort by the original column (it's perfectly legal to ORDER BY
a column that's not in your result). However you must alias the
converted date column to a different name then.

BTW, I do not recommend to alias computed result columns identical
to existing columns. This will easily cause confusion.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/