asked on
Sorting PHP Dates With DATE_FORMAT
I have a tinytext field that takes values in the format date("d M, Y H:i") and want to sort it in descending chronological order.
I have tried the following SQL, using the Reference Manual as a guide, but it will not sort in descending order:
SELECT field FROM table ORDER BY DATE_FORMAT(field, '%d %b, %Y %H:%i') DESC
If I should alter the format in which the field takes values using the date function, please supply a method of altering the existing values, along with the amended date function and SQL query.
I have tried the following SQL, using the Reference Manual as a guide, but it will not sort in descending order:
SELECT field FROM table ORDER BY DATE_FORMAT(field, '%d %b, %Y %H:%i') DESC
If I should alter the format in which the field takes values using the date function, please supply a method of altering the existing values, along with the amended date function and SQL query.
PHPMySQL Server
Last Comment
lhutton
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you!
ASKER
angelIII: tinytext, because that was what I was given to begin with, ages ago; not my choice.
to start with: why a tinytext field, and not a datetime data type? would make your life much easier.
now: DATE_FORMAT formats a date into a string.
you HAVE a string, and want it sorted by date value, hence you want to use the function str_to_date:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date