Ordering By Date but Filed is a Varchar in MYSQL

[MrSpeed]

I have a web page that displays new stories sorted from the newest to the oldest. It worked great until the new year started and the items created in 2006 are now at the bottom of the list. The cause of the problem is that my 'date' field is a varchar in MySQL.

Here is my query:

SELECT distinct posted FROM news WHERE category LIKE 'Tech' ORDER by posted DESC

The 'posted' field is the date the story was created and is a varchar(10).
The format of the date that I insert is 12-03-2005. So I know why my query gets confused with 01-15-2006.

I tried to change the field type to DATE but then all the dates become 0000-00-00 since MySQL wants the date to be YYYY-MM-DD and my format is MM-DD-YYYY.

Is there a simple way to fix the display order by a different SELECT statement?

Otherwise I guess I can export all the data,change it in Excel and import it back in YYYY-MM-DD format and of course change the PHP script that writes to the DB.

[txbakers]

you might be able to use the LEFT() function to get a better sort.

But the best solution is to make a date field and learn to input the data in the YYYY-MM-DD format. You'll be glad you did!

[ixyst]

Something like this:

ORDER BY STR_TO_DATE(dateColumnName, '%d/%m/%Y')

will help, but will work pretty slow (especially for big tables)... You should really consider changing the data type on your column...

[MrSpeed]

I went back and changed the data type. It was just the right thing to do.