Date sorting in MySQL

Question

I am capturing dates in the following format:

Mon, February 14, 2011, 08:22:34 AM

When I sort posts by the most recent date/time by using ORDER BY date_time DESC, sometimes it sorts it correctly and sometimes it doesn't. So is there a better way to capture data so that the sorting will be done correctly?

For instance, work ten entries in a row it might sort properly, that is it will show the most recent posts first. Then after that it might put the most recent one at the very bottom

Answer 1

Per discussion on OP, you are storing the date as a string. MySQL has many date sorting functions that can sort accurately, but the must be used in conjunction with a DATE type field. You should either convert the field type to date/datetime, or perform a cast function to turn them into date types for the purpose of sorting. Like this:

SELECT CAST(datefield as date) AS date_format ORDER BY date_format DESC

Answer 2

You should be using a DATETIME field instead of a VARCHAR field for storing dates...

http://dev.mysql.com/doc/refman/5.1/en/datetime.html

This will allow you to sort correctly as well as use MySQL datetime functions in queries on your datetime fields.