[SOLVED] Help with sorting by dates formated like 01/05/2009

[Presto-X]

Hello everyone,

 

I have a large database with a list of dates, the system preformats the dates and saves them in this format 01/05/2009. but when I use mysql to sort by date it is not turning out like I want.

 

This is how my dates are getting displayed:

01/05/2009

03/24/2009

09/15/2008

10/15/2008

11/03/2008

 

This is the line I'm using:

$query_upcourses = "SELECT * FROM jos_courses_dates ORDER BY course_start_date ASC";

 

If you need any other information let me know.

[Mchl]

Store your dates as DATE, DATETIME or TIMESTAMP and not as strings. Will help a lot.

[Presto-X]

Thanks for the reply Mchl, I updated my date rows from varchar to date. I'm using a javascript date selector, found here: http://www.dynamicdrive.com/dynamicindex7/jasoncalendar.htm they give you the following date formats, what format should I use when saving to my database?

 

YYYYMMDD

YYYY-MM-DD *

YYYY-DD-MM *

YYYY/MM/DD *

YYYY/DD/MM *

YYYY-DD-MON *  **

YYYY-MON-DD *  **

MM-DD-YYYY *

DD-MM-YYYY *

MM/DD/YYYY *

DD/MM/YYYY *

DD-MON-YYYY *  **

MON-DD-YYYY *  **

[Mchl]

Any one that fits you. Using PHP functions date can be transformed to a format that will be acceptable by mySQL (YYYY-MM-DD)

[Presto-X]

sweet, thanks for the help, I used YYYYMMDD and the javascript and mysql seem to both like that format, so I'm going to brush back up on date formating.