[Resolved] Query messing up

Sharif

Why does this query:

SELECT id, DATE_FORMAT( message_date, '%m/%d/%y' ) AS message_date, message, first_name
FROM staff
WHERE STATUS = '1'
ORDER BY message_date DESC

Order dates like this:

id message_date
6 | 12/28/03
5 | 11/26/03
2 | 10/07/03
1 | 01/09/04
3 | 01/08/04

Isn't it supposed to list 2004's dates first?

jwp

i thought it did the first value first..........

Sharif

Where that query is executed through a while loop... i didn't post all the code.

leatherback

I'd think so.
Not sure what is causing this to happen. Perhaps mysql is interpreting teh datestring as a string..? (Sounds too weird to be true though).

What you could do.. Instead of having the date field: DATE_FORMAT( message_date, '%m/%d/%y' ) AS message_date

try to use: DATE_FORMAT( message_date, '%m/%d/%y' ) AS message_out_date

and keep the order by statement as is. Then you are actually sorting by the timestamp in the database.

Sharif

Thank you. Solved the problem.