Page 1 of 1

MySQL DATE_FORMAT problem

Posted: Fri Jan 02, 2009 6:32 pm
by Auselan
Am trying to let my users submit Dates in the UK format e.g. 29/09/1978 into my MySQL table "job" via this update string:

Code: Select all

"UPDATE job SET practice = '$practice', date_posted = CURRENT_TIMESTAMP, job_start_date = '$job_start_date', job_end_date = '$job_end_date', contact = '$contact', session_visible = '$session_visible', accepted_by = '$accepted_by', job_details = '$job_details' WHERE id='$id' "
I can nicely change the display format for when the result is later published, using

Code: Select all

DATE_FORMAT('$job_start_date', '%d/%m/%Y');
but what I can't figure out is how to post new data into the table. using PHPMyAdmin I can effectively modify a record like this:

Code: Select all

UPDATE `job` SET `job_start_date` = '$job_start_date' WHERE `id` = 1;
but can't get the syntax right to convert the entry put in by the user. I've tried this: without success

Code: Select all

UPDATE `job` SET `job_start_date` = DATE_FORMAT('$job_start_date', '%d/%m/%Y') WHERE `id` = 1;
It gives me no error message, just doesn't update the record at all - it resets it to its Default which is 0000-00-00. I've now searched for hours using forums and googling but the MySQL documentation does not contain examples that illustrate how I can tackle this. Can anyone help?

Re: MySQL DATE_FORMAT problem

Posted: Fri Jan 02, 2009 7:50 pm
by califdon

Re: MySQL DATE_FORMAT problem

Posted: Fri Jan 02, 2009 8:04 pm
by VladSun

Re: MySQL DATE_FORMAT problem

Posted: Fri Jan 02, 2009 9:14 pm
by califdon
Hey, Vlad, that's one I didn't know about, thanks! That could be very handy for a little task I have to work on. So many functions, so little time!

Re: MySQL DATE_FORMAT problem

Posted: Sat Jan 03, 2009 4:28 pm
by Auselan
Thanks guys - I figured out what works for me which is:

For input, in the MySQL query I can use

Code: Select all

mydatestampcolumnname = STR_TO_DATE('$mydatestampvariable', '%d/%m/%Y')
For output - instead of echo functions, I can use

Code: Select all

print date('d/m/Y', strtotime($mydatestampvariable))
I even figured out how to get a lil calender widget to generate the dates for me! :-D