[RESOLVED] ordering by date

steveDD

I currently have a form as follows

$sql = "select id, name, mcontact from onlinet where mcontact !='NA' and visable !='0' order by mcontact";

Mcontact has a date in in the formate dd/mm/yyyy and I am trying to get it so it will order them with the soonest date at the top. at the minute it only orders by the first 2 in the dd column and the years and months are mixed up.

How can I order this by date?

dagon

what's the field type for mcontact ?

steveDD

it is normal I havent changed it var char?

dagon

there is no 'normal' it should be one of the date\time field types if you expect to be able to sort by it

10.3. Date and Time Types

steveDD

i manually add the date and time to the database in the above format. When i set up the database i just have the field its normal properties as it default.

steveDD

ah i see so he i change all my dates to the correct format in my database e.g yyyymmdd it should be in a valid form and the when i select the mcontact i need to change it to the correct format. Is this right? And how would i convert it.

Kudose

SELECT DATE_FORMAT(mcontact, '%m-%d-%Y') AS dateformatted FROM onlinet ORDER BY mcontact DESC

echo $row['dateformatted'];

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

steveDD

sorted thank you