Date sorting problem with Date_Format?

2008-02-08 Thread mikesz 
Hello mysql,

I have a PHP script that is running the following query:

SELECT `Messages`.`ID`,
`Messages`.`Sender`,
`Messages`.`Subject`,
 DATE_FORMAT( `Messages`.`Date`, '%D %M %Y' ) AS Date,
 LEFT(`Messages`.`Text`, 200 ) AS Preview,
`Messages`.`New`,
`Profiles`.`NickName`,
`Profiles`.`RealName`,
`Profiles`.`Status`,
`Profiles`.`ID` as pID
 FROM `Messages`
 LEFT JOIN Profiles ON Messages.Sender = Profiles.ID 
WHERE Recipient = 5 ORDER BY `Date` DESC;

The problem is that the ORDER BY `Date` DESC is generating random
results?

If I run this same script interactively in an sql window it works correctly.

If I remove DATE_FORMAT function and just call `Date`, the script sorts and 
displays
correctly.

Is this a bug?
-- 
Best regards,
 mikesz  mailto:[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Date sorting problem with Date_Format?

2008-02-08 Thread mikesz 
Hello mysql list,

As is very often the case, five minutes after I posted this, I found
the problem or solution, not sure it was the problem as I am not
convinced that mysql ought to get confused so easily. I changed

DATE_FORMAT( `Messages`.`Date`, '%D %M %Y' ) AS Date,

DATE_FORMAT( `Messages`.`Date`, '%D %M %Y' ) AS FmtDate,

and used the FmtDate reference to display the query results and it worked fine.

-- 
Best regards,
 mikeszmailto:[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]