i got a release_date row in my database now i want too display the latest date on top
my query now:$sql = "SELECT * FROM games ORDER BY release_date ASC";
but this ORDER BY just get the first digits (day) of the date Now i get:
03-11-2014 12-11-2013 14-07-2011 16-03-2012
it needs to be:
03-11-2014 12-11-2013 16-03-2012 14-07-2011
Use STR_TO_DATE to turn your date string into a real date.
SELECT * FROM games
ORDER BY STR_TO_DATE(release_date, '%d-%m-%Y') DESC
You can also use this function to refomat your date into the default format and then change the column data type to date.
The solution given by the above answer worked for your situation.
BUT, the best thing to do is to change the column type to datetime, and not by calling functions to cast str to datetime in your query, because you will have to modify all your queries where you use the 'release_date' field.
If you change the data type, you are doing the right way.
Its better to use date type for your date field if you have stored it as a string like varchar you can change the string values to date object while running query but you cannot make use of any index to that by applying some date function what i suggest you to alter your schema and change the type of your date field to as real date below are the steps how you can change it
Step 1 add new column as acopy of your release_date
ALTER TABLE t ADD COLUMN `release_date_copy` DATE NULL AFTER `release_date`;
Step 2 save your date string converting it as real date to new column i.e release_date_copy
update t
set release_date_copy = STR_TO_DATE(release_date, '%d-%m-%Y'),
release_date = null;
Step 3 change the type of your release_date column to date
ALTER table t CHANGE `release_date` `release_date` DATE NULL;
Step 4 copy data from release_date_copy to your real column release_date
update t
set release_date = release_date_copy;
Step 5 delete the copy of your column
ALTER TABLE t DROP COLUMN `release_date_copy`;
Now you can simply query it in descending manner and you can also make use of an index
select * from t
order by release_date desc