MySQL DATE_FORMAT problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Auselan
Forum Newbie
Posts: 18
Joined: Sat Dec 27, 2008 7:04 am

MySQL DATE_FORMAT problem

Post 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?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: MySQL DATE_FORMAT problem

Post by califdon »

User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL DATE_FORMAT problem

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: MySQL DATE_FORMAT problem

Post 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!
Auselan
Forum Newbie
Posts: 18
Joined: Sat Dec 27, 2008 7:04 am

Re: MySQL DATE_FORMAT problem

Post 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
Post Reply