I have a table where there is a date field. The values of the items inserted there are full date names.
How will I be able to sort the selected result by proper date?
Example table
February 10, 2010
January 5, 2010
January 4, 2010
January 5, 2009
January 6, 2010
March 21, 2010
What I want to happen
January 5, 2009
January 4, 2010
January 5, 2010
January 6, 2010
February 10, 2010
March 21, 2010
Is there a mysql function for this?
Here is my query. Date field is named
date
$sql = "SELECT * FROM `$table` ORDER BY date";
Unfortunately, this only sorts the result alphabetically. How should I modify/change the query?
Your future insights and feedback are greatly appreciated! :)
UPDATE
So the problem is that the field is
string/varchar
. Is it possible to change this after the data has been stored? And would the query above automatically sort it correctly?
This is because the column in the database is setup as a
string
/varchar
, versus a datetime
field. There are a few ways to approach this, but the easiest would be to convert your fields to datetime fields instead.
An inefficient solution would be to do:
$sql = "SELECT * FROM `$table` ORDER BY STR_TO_DATE(`date`)";
This will convert your strings to dates, and then sort them, but will have to do this for every row in your database which could get cumbersome. Bottom line: If you're working with datetimes, use a
datetime
field.
0 comments:
Post a Comment