Page 1 of 1

[SOLVED]Order by dates...

Posted: Fri Sep 22, 2006 10:13 am
by aquanutz
I am trying to bring back the data from my database in order of the starting date and ending date.. however, I am not able to do so. This is my query:

Code: Select all

select num, DATE_FORMAT(startDate,'%b %e, %Y') as startDate, DATE_FORMAT(endDate, '%b %e, %Y') as endDate, reason from exceptionDates order by startDate, endDate;
and this is the result that I get:

Code: Select all

+-----+--------------+--------------+------------------+
| num | startDate    | endDate      | reason           |
+-----+--------------+--------------+------------------+
|   9 | Apr 14, 2007 | Apr 14, 2007 | Special Closing  |
|   6 | Dec 23, 2006 | Dec 29, 2006 | Christmas Break  |
|   7 | Dec 30, 2006 | Jan 1, 2007  | new year's break |
|   8 | Mar 2, 2007  | Mar 11, 2007 | spring break     |
|   4 | Nov 10, 2006 | Nov 12, 2006 | veteran's day    |
|   5 | Nov 21, 2006 | Nov 26, 2006 | thanksgiving     |
|   3 | Oct 6, 2006  | Oct 10, 2006 | Fall Break       |
|   2 | Sep 1, 2006  | Sep 4, 2006  | Labor Day        |
+-----+--------------+--------------+------------------+
as you can tell, Apr 14, 2007 comes after Dec 23, 2006. Is there a glaring error in my sql statement?

here is the make up of the table:

Code: Select all

+-----------+----------+------+-----+---------+----------------+| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| num       | int(11)  |      | PRI | NULL    | auto_increment |
| startDate | date     | YES  |     | NULL    |                |
| endDate   | date     | YES  |     | NULL    |                |
| reason    | char(25) | YES  |     | NULL    |                |
+-----------+----------+------+-----+---------+----------------+
Any thoughts would be great. Thanks.

Posted: Fri Sep 22, 2006 10:21 am
by aquanutz
I've also tried to use:

Code: Select all

select num, DATE_FORMAT(startDate,'%b %e, %Y') as startDate, DATE_FORMAT(endDate, '%b %e, %Y') as endDate, reason from exceptionDates order by startDate, endDate desc;
and I get this:

Code: Select all

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

Code: Select all

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

Code: Select all

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

Posted: Fri Sep 22, 2006 10:29 am
by aquanutz
Alright, apparently the formatting of the date is what is messing with the results. I took out the formatting and it works just fine now. Any thoughts on how to get it to work with the formatting because that is how I need to display the dates?

I suppose I could just make a nested query after I get the order and then look up that specific date by it's 'num' and format it that way.

Posted: Fri Sep 22, 2006 10:32 am
by hawleyjr
aquanutz wrote:Alright, apparently the formatting of the date is what is messing with the results. I took out the formatting and it works just fine now. Any thoughts on how to get it to work with the formatting because that is how I need to display the dates?

I suppose I could just make a nested query after I get the order and then look up that specific date by it's 'num' and format it that way.
It's not the formatting of the date... Try this:

Code: Select all

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;

Posted: Fri Sep 22, 2006 10:37 am
by aquanutz
EDIT: I thought that did the trick, but apparently it didn't. This was the result:

Code: Select all

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

Posted: Mon Sep 25, 2006 11:17 am
by aquanutz
buuump... anyone?

Posted: Mon Sep 25, 2006 11:33 am
by onion2k
The problem is that the dates are being sorted as strings instead of as dates. DATE_FORMAT() returns a string. It's doing that because you're using the column name as it's DATE_FORMAT() alias.

Code: Select all

select num, DATE_FORMAT(startDate,'%b %e, %Y') as formattedStartDate, DATE_FORMAT(endDate, '%b %e, %Y') as formattedEndDate, reason from exceptionDates order by startDate, endDate;
That'll sort it.

Posted: Mon Sep 25, 2006 11:45 am
by aquanutz
Thanks so much! I can't believe I didn't even notice that, it makes perfect sense. Thanks again.