mysql sorting by date

[arbitter]

Hi there

 

In my database I have a date in the form 'dd/mm/yyyy'. Now, I would like to sort my queries by date... Is there a way to do that? Or is the only option to separate the day/month/year in the database?

 

Thanks in advance.

[AyKay47]

Is the field a varchar or a datetime field?

[arbitter]

well it's varchar, I presume it should be datetime in order for it to work?

[AyKay47]

well it's varchar, I presume it should be datetime in order for it to work?

You presume right, change it to a datetime format and use the MySQL function date_format();

[Muddy_Funster]

it should be date field type simply because you are using it to store dates...if if it doesn't work.

[Pikachu2000]

You can order by the field as long as the format is right, YYYY-MM-DD, although it really should be an appropriate field type for numerous other reasons.

[arbitter]

Okay just to be sure of what I need to do;

I have input fields, one for day, one for month, and one for year.

 

What should I do to the fields so I can store it in the datetime field?

[Pikachu2000]

Are you storing the time in the table as well, or just the date?

[arbitter]

I only store the date, it's for a general calendar.

[Pikachu2000]

Then use a DATE data type for the field, and just concatenate the form values in the proper order, YYYY-MM-DD. It wouldn't be a bad idea to validate the date with checkdate at the same time you validate the rest of the form data to prevent invalid dates from ending up in the table with values of 0000-00-00. Probably the best way to handle user input for dates is with a jQuery datepicker, and add <select> fields for the year, month and day in a <noscript> block, if you want to give that  shot.

 

Do you have misformatted data already in the table that you need to convert as well, or is this a new table?

[arbitter]

It seems to work! I only did the checkdate() though, no jQuery stuff.

 

And it's a new table, so no problems! The dates are so confusing, in america it's mostly mm/dd/yyyy, where I live it's dd/mm/yyyy, and now yyyy/mm/dd

 

It wasn't that much adjusting from the script I had, luckily!

 

Thanks a bunch!