Sort By Date

Anon

Im creating a database driven website for a news posting system but i have a date field in a mysql database but when i try and sort it by the date using

"SELECT * FROM news ORDER BY date DESC";

but im getting the following results

06/02/2002
06/02/2002
06/02/2002
01/12/2001
01/12/2001
01/12/2001
01/02/2002
01/02/2002
01/02/2002
01/01/2002
01/01/2002
01/01/2002

Please Help a newbie!

Anon

is it a date field in mysql? or is it a text field that you use as a date?

Anon

I have the same problem.. now that I look at it I have 3 fields that are all char(2) for date_mm, date_dd, date_yy.

When I query SELECT * FROM news ORDER BY date_mm, date_dd DESC

mm dd yy
01,03,02
01,04,02
01,08,02
01,10,02
02,11,02
12,12,01
12,13,01
12,26,01

Should I only use one field for the date, or how would you suggest doing this. Thanks

Anon

Bill its a text field just storing a date

Anon

To explain further, I have a form that has a date_day, date_month, date_year fields, that input the data entered into the corresponding database fields date_dd, date_mm, date_yy. I need to sort the records DESCending, the most recent dates first. Im not sure how to accomplish this with 3 separate fields for month, day , year. On your form you almost need 3 different fields for user input though.

I want it to sort like this:

mm dd yy
02,11,02
01,10,02
01,08,02
01,04,02
01,03,02
12,26,01
12,13,01
12,12,01

Instead of :
SELECT date_mm, date_dd, date_yy FROM news ORDER BY date_mm, date_dd DESC
01,10,02
01,08,02
01,04,02
01,03,02
02,11,02 <--notice where february is
12,26,01
12,13,01
12,12,01

Anon

You need to do SELECT date_mm, date_dd, date_yy FROM news ORDER BY date_yy DESC, date_mm DESC, date_dd DESC

Anon

the problem with a text field storing date is that you can't order it, unless you store it in the format YYYYMMDD. The format YYYYMMDD allows you to order it because the year field is first, followed by the month, then year.

I am used to oracle which I could just use the to_date() function. Check for something simular in the MySQL documentation.