02-19-2009, 07:20 PM
I am trying to update the date and time in a table that I have.
When I try to update it, the value in the MySQL database just gets reset to 00/00/00 00:00:00.
Here is the code I'm using in PHP:
"UPDATE Auction SET
endDateTime = '11/01/2009 13:23',
WHERE auctionID = '$auctionID'";
return mysql_query($sqlCommand, $this->connection);
Why does this not work?
02-19-2009, 07:56 PM
I believe you need to include seconds. Though, MySQL is very flexible with its acceptance of various datetime formats, as can be seen in the manual here:
But I did not see where MySQL accepts omission of seconds.
If you code error checking in your script you'll probably be told exactly what is wrong with the query.
02-19-2009, 08:04 PM
Thanks for the link. This works:
$sqlCommand = "UPDATE Auction SET endDateTime = '2009-11-01 13:23:00' WHERE auctionID = '1'";
But the date has to be in YYYY-MM-DD format.
How can I insert the date in this format:
DD/MM/YYYY (the delimiter is / and the day and month comes before the year)?
02-19-2009, 08:59 PM
You'll need to reformat your string.
02-19-2009, 09:32 PM
You can also use a number, instead of a string, if that's easier for you:
$sqlCommand = "UPDATE Auction SET endDateTime = 20091101132300 WHERE auctionID = '1'";
Is auctionID *REALLY* a TEXT field??? If it's numeric, then you should NOT put '...' around the 1 value.
In any case, you'll need to use PHP code to generate the year-first format, either as a number or string.
02-19-2009, 11:41 PM
OldPedant, you do not need to use PHP to generate the date in requested format, you can do it with DATE_FORMAT directly in mysql query.
Zachbb, make sure your date and time is in a DATETIME field type. that way you have access to all date and time functions for that field. you can use the DATE_FORMAT function to output the data in any number of formats.
02-19-2009, 11:45 PM
Could you look at this thread
02-20-2009, 02:24 AM
Would you explain how you could use DATE_FORMAT() to convert an *INCOMING* date of the wrong format to the form needed by MySQL????
I guess I could see using STRING functions to do that, followed by a CONVERT call, perhaps. But unless you did that in a stored proc, you'd have to pass the date-as-string value in to the code at least 3 times.
Anyway, I don't see how DATE_FORMAT(), which is designed to format an *existing* DateTime value (e.g., a column in a MySQL table) to a string. So far as I can tell--even after reading the docs through a half dozen times--it is not capable of doing the reverse, converting a string of a given format into a DATETIME value.
What am I missing???
02-20-2009, 03:15 AM
sorry STR_TO_DATE to format an incoming string. and it exists in mysql.
02-20-2009, 03:32 AM
Well I will be *#&*!?!*@#'d
In 2 years of working with MySQL I never saw that! I wrote a nice neat Java function (for JSP pages) to do the work.
DOH on me and thank you!!