MySQL Date Ordering Problem

[Limit]

My dates go into the database looking like this:-

Sat Dec 11, 2004 10:15 am

and get pulled out like this:-

Sat Dec 11, 2004 10:15 am

However when i put them in descending order they order like this:-

Sat Dec 11, 2004 10:15 am
Mon Dec 13, 2004 10:15 am
Fri Dec 10, 2004 20:49 pm

How can i get them to order correctly and formatted like they are now. Here is the table:-

date varchar(35)

[digitman]

show the query you are using?

[Limit]

the query is:-

SELECT * FROM ls_client_bugs ORDER BY date DESC

do you think it has anything to do with the date being stored in the database as Sat Dec 11, 2004 10:15 am, etc..

[Limit]

thats the wrong one its actually:-

SELECT * FROM table2 ORDER BY dateposted DESC

[littleFella]

The problem is not with the query but with the datatype used to store the date and time. You are using data varchar(35). This is asking for trouble for at least two reasons:

- you are using a reserved word "date" for a column name. I'm not even sure why your mySQL allowed this.
- as you see you dates are sorted in alphanumeric order, not in the order in time because you use a wrong data type

To fix your problem you have some options.
Personally I would change the datatype of the field in question into the DATETIME type. You can then use PHP date formatting functions for correct display of the datetime values using days of week etc. Remember, you don't have to store data in the exact format in which you display it. I really suggest that you read up on data types.

If the above is not possible then you may add a secondary column (call it sort_date) which would store numeric date values for ever value stored in your current date varchar(35) column. So data in that sort_date column would look something like this:

YYYYMMDDHHMM (e.g. 200412011201 for Dec 01. 2004 at 12.01PM)

and your query would then look like this:

SELECT * FROM ls_client_bugs ORDER BY sort_date DESC


However, since the free versions of mySQL has no stroed procedures the second solution would require some sacrifices in performance.

[Limit]

k i've changed it to DATETIME but how do i format the DATETIME to what i had before.

This is date bit of my code:-

echo "<tr bgcolor=\"$bgcolor\">
<td>".$displayrow['date']."</td>
<td>".$displayrow['name']."</td>
<td><a href='report.php?id=".$displayrow['iid']."'>".$displayrow['title']."</a></td>
<td>".$displayrow['status']."</td>
</tr>";

[littleFella]

http://www.php.net/manual/en/function.date.php

[Limit]

changed to:-

$query = "SELECT id, name, title, status, DATE_FORMAT(date, '%a %b %d, %Y %H:%i %p') as date FROM table2";

which formats date time to what i want but it still sorts it like this:-
Thu Dec 09, 2004 19:30 PM
Fri Dec 10, 2004 19:30 PM
Fri Dec 10, 2004 19:30 PM
Mon Dec 13, 2004 19:30 PM
Sat Dec 11, 2004 19:30 PM

[littleFella]

this time you forgot "order by"

[Limit]

still doesn't work

$query = "SELECT id, name, title, status, DATE_FORMAT(date, '%a %b %d, %Y %H:%i %p') as date FROM table2 ORDER BY date DESC"

orders dates like this:
Thu Dec 09, 2004 19:30 PM
Sat Dec 11, 2004 19:30 PM
Mon Dec 13, 2004 19:30 PM
Fri Dec 10, 2004 19:30 PM
Fri Dec 10, 2004 19:30 PM

instead of real date posted order

[Limit]

ignore that i have it working now it was because i was calling date, date again instead of date2