Professional Web Applications Themes

Won't bring back Dates correctly.. - MySQL

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

  1. #1

    Default Won't bring back Dates correctly..

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

    aquanutz@gmail.com Guest

  2. #2

    Default Won't bring back Dates correctly..

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

    aquanutz@gmail.com Guest

  3. #3

    Default Re: Won't bring back Dates correctly..

    On 25 Sep 2006 09:11:25 -0700, com wrote:
     
    ^^^^^^^^^ ^^^^^^^^^

    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 StartDate,
    DATE_FORMAT(endDate, '%b %e, %Y') as EndDate, reason from
    exceptionDates order by startDate DESC, endDate DESC;

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

    --

    Dennis K.
    Dennis Guest

  4. #4

    Default Re: Won't bring back Dates correctly..

    com wrote:
     
     
    ....
     

    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/
    Axel Guest

Similar Threads

  1. bring back text as hyperlink from access .asp
    By tyrantdj in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: March 15th, 12:18 PM
  2. HOWTO: Bring that row back into focus ...
    By So in forum ASP.NET Data Grid Control
    Replies: 5
    Last Post: May 13th, 01:31 PM
  3. Bring Back the Verity Forums
    By leontova in forum Coldfusion Server Administration
    Replies: 1
    Last Post: April 6th, 09:20 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139