MySQL order by problem

[Anon]

I have a field that contains dates in the following format: M/D/YY, so today would be 12/10/01 and yesterday would be 12/9/01. When I order the dates in descending order from newest to oldest, it puts 12/10/01 last, even though it is the newest. I realize it's because it sees "10" and thinks "1" therefore putting it after 9, 8, 7, 6, etc. Is there a way to fix this? I know I could store the date as a datestamp and then convert it after pulling it from the DB. If that is the best solution, then what would the code for that be? TIA!

[Anon]

Sounds like your date field is really a character field and you're getting a character sort. Use a date field instead and format the output with the date function (http://www.php.net/manual/en/function.date.php)

Hank

[herve]

Yes your problem arise because you have use
a CHAR type for the field instead of
a DATETIME type.

Use a DATETIME and your sorting pb will go
away :-)

For extracting date from MySQL format I've coded a small function :

function ExtractMySQLDate ($MySQLDate)
{

global $SQLDate;

$SQLDate["year"] = substr ($MySQLDate,0,4);
$SQLDate["month"] = substr ($MySQLDate,5,2);
$SQLDate["day"] = substr ($MySQLDate,8,2);
$SQLDate["hour"] = substr ($MySQLDate,11,2);
$SQLDate["min"] = substr ($MySQLDate,14,2);
$SQLDate["sec"] = substr ($MySQLDate,17,2);
}

The parameter is the date in MySQL format
and the function will feed global var
"SQLDate" which is an associative array

Example of use :

$temp_date = $myrow["date_creation"];
ExtractMySQLDate($temp_date);
$day = $SQLDate ["day"];
$month = $SQLDate ["month"];
$year = $SQLDate ["year"]