help with sorting dates

[scialom]

Hello all,

I have a table in the db that store some dates. The date field is just a varchar type.
So the dates are inserted like this 31/07/2008, 19/09/2008 etc...

now when I run an sql statement and ask to sort it by date desc the 31/07/2008 appears before 19/09/2008

is there a way to get a way with it without store everything in array convert it to php date format and sort the array?

thanks!

[bpat1434]

Sure! Change your DB column type to DATE 😉 Then there's nothing really more to do.

One thing you could also do is select a column that changes your date field to a unix timestamp, and sort by that.

SELECT *, UNIX_TIMESTAMP(`date_field`) AS `date_field`
FROM `tablename`
ORDER BY `date_field` DESC

[scialom]

thanks, but due to the structure of the application I can't change the field type.
I tried with the timestamp but it gives me 0 for each entry

[NogDog]

Check out the MySQL STR_TO_DATE function.