View Full Version : How do I update DateTime?

02-19-2009, 08: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:
$sqlCommand =

endDateTime = '11/01/2009 13:23',
WHERE auctionID = '$auctionID'";

return mysql_query($sqlCommand, $this->connection);

Why does this not work?

02-19-2009, 08: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, 09: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, 09:59 PM
You'll need to reformat your string.

Old Pedant
02-19-2009, 10: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-20-2009, 12:41 AM
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-20-2009, 12:45 AM
Ok thanks..

Could you look at this thread

Old Pedant
02-20-2009, 03: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, 04:15 AM
sorry STR_TO_DATE to format an incoming string. and it exists in mysql.

Old Pedant
02-20-2009, 04: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!!