On Monday 17 February 2003 21:06, Ken Goff wrote: > I have encountered what appears to be a bug in MySQL regarding ORDER > BY related to a date field when also used in conjunction with > DATE_FORMAT() or UNIX_TIMESTAMP(). When these functions are applied > to my date field, MySQL ignores any included ORDER BY within the > query. I first noticed this bug using PHP with MySQL but have > confirmed it occurs when entering the query directly within Terminal. > > The table consists of the following 4 fields with their data types > described: > > EventId - number - mediumint(10) - auto_increment > EventDate - date > Event - text > EventOrder - number - mediumint(5) > > The following query sorts properly: > > SELECT * FROM EventList ORDER BY 'EventDate', 'EventOrder' LIMIT 50; > > The following ignores ORDER BY and instead sorts by EventId: > > SELECT EventId, DATE_FORMAT(EventDate, '%c-%e-%Y') as > FormattedEventDate, Event, EventOrder FROM EventList ORDER BY > 'EventDate', 'EventOrder' LIMIT 50; > > So does the following sort by EventId instead of using the ORDER BY: > > SELECT EventId, UNIX_TIMESTAMP(EventDate), Event, EventOrder FROM > EventList ORDER BY 'EventDate', 'EventOrder' LIMIT 50; > > Is there any way around this bug? I want to list dates in US style of > "m-d-yyyy" instead of "yyyy-m-d".
It works fine for me. Could you provide example of wrong ordering? Please, include also EvendDate column in the output. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php