Mysql Order By Date is having some issues...

[youaretheone]

Hi , I have a site..where i need to list records based on the modified date...im storing the date in the table like this "2 July, 2009, 8:37 am"....and the query i write is "SELECT * FROM Table ORDER By profile_updated_date DESC"

profile_updated_field will have values similar to 2 July, 2009, 8:37 am....and the listed records order is not correct...the data type for this fields is varchar...what could be the issue..i tried many way to solve this..please suggestion a solution..
Really appreciate your help...

Regards,
Unni

[Annalyzer]

Well, it seems to me that if you have a field that holds dates and times, then you might want to think about using a datetime type instead of a varchar. Varchar is going to sort in alphanumeric order exactly the way they are written which puts"1 Dec" before "1 Nov" before "1 Oct" and so on. Datetime will sort in the order you are expecting.

[Atli]

Hi.

I'd have to agree with Annalyzer.

Dates should always be stored using the date types: Date, Time, DateTime or Timestamp.
Unless you have a very very very good reason not to.

You should never store dates as strings (VarChar, Char, Text).
If you store them as strings (or integers, as some people seem to think is best) you will have to manually handle how they are sorted, compared, etc... because MySQL will simply look at them as strings (or numbers) and treat them accordingly.

So my advice to you is: convert your string date fields into real date types.
Then MySQL will sort them for you.