Insert a date into DB

[mattyboi]

I have searched the forum and there is a lot of things about dates, but not quite what I am looking for.

I have a form, with several date fields. Intial date, end date, appt. date, etc. I would like to insert these dates into seperate fields in a table. Thes fields will have the DATE format.

I noticed that MySQL formats dates like so: yyyy/dd/mm

My users will be formating the date like so when they fill out the form. mm/dd/yy.

I want to insert the different dates into the table. Later, I will display the records and ORDER BY the different dates depending on which date you would like to order by.

Will I need to reformat the dates to the MySQL format in order to ORDER BY correctly. If so, how would the insert query look?

Thanks

[guelphdad]

What type of field? Is it a text field? If so what prevents the following dates from being entered:

mm/dd/yy
dd/mm/yy
mm/dd/yyyy

You can look up the use of str_to_date in mysql and manage the dates that way. It allows you to accept the date in the format you suggest and then format it to the needed yyyymmdd date type for the database.

The problem of course is that you could end up with different formats as suggested above.

[raf]

I noticed that MySQL formats dates like so: yyyy/dd/mm

the only valid valueformat for DATE's in MySQL is yyyy-mm-dd

My users will be formating the date like so when they fill out the form. mm/dd/yy.

I want to insert the different dates into the table.
Then you'll first need to reformat the values in your server side scipting language or inside the insertstatement.
Inside the insertstatement, you'd need to use something like
INSERT INTO tablename (field1, datefield) VALUES (1,STR_TO_DATE('02/12/99', '%m/%d/%y'))
where the 02/12/99 needs to be replaced by the posted value.

But it's better to reformat the value with your server side language because that allows better formatchecking and errorhandeling...

Later, I will display the records and ORDER BY the different dates depending on which date you would like to order by.

Will I need to reformat the dates to the MySQL format in order to ORDER BY correctly.
if the datevalues are entered into a column of type DATE, then an ORDER BY on that collumn will sort them in chronological order