Why can I INSERT a DATETIME record into mysql but I can't UPDATE it?

Please post the UPDATE query, fully resolved, or if you're using PDO, show us all the moving parts.

Please post the CREATE TABLE statement so we can see how the column was defined, Thanks.

What error do you get back from the db?

If you are using MySQLi you can dump the error property immediately after calling the update query.

Something like this will really help us

Adapt to your environment

$query  = "UPDATE ....";
$conn->query($query);
echo "Query: {$query}<br/>";
echo "Error: {$conn->error}<br/>";
...

Select allOpen in new window

This seems to work correctly.  The original and updated DATETIME values are printed.  The TIMESTAMP column shows the time of the update, which matches the new DATETIME value.

Docs here: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html
Example here: https://iconoun.com/demo/mysqli_datetime_example.php

<?php // demo/mysqli_datetime_example.php
/**
 * Demonstrate some of the basics of MySQLi with DateTime Columns
 *
 * References for PHP and MySQL(i):
 *
 * http://php.net/manual/en/mysqli.overview.php
 * http://php.net/manual/en/class.mysqli.php
 * http://php.net/manual/en/class.mysqli-stmt.php
 * http://php.net/manual/en/class.mysqli-result.php
 * http://php.net/manual/en/class.mysqli-warning.php
 * http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
 *
 * http://php.net/manual/en/mysqli.construct.php
 * http://php.net/manual/en/mysqli.real-escape-string.php
 * http://php.net/manual/en/mysqli.query.php
 * http://php.net/manual/en/mysqli.errno.php
 * http://php.net/manual/en/mysqli.error.php
 * http://php.net/manual/en/mysqli.insert-id.php
 *
 * http://php.net/manual/en/mysqli-result.num-rows.php
 * http://php.net/manual/en/mysqli-result.fetch-array.php <-- DO NOT USE THIS
 * http://php.net/manual/en/mysqli-result.fetch-object.php
 */
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, xdate DATETIME    NOT NULL DEFAULT '0000-00-00 00:00:00'
, xwhen TIMESTAMP   NOT NULL
)
"
;

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW THE RESULTS OF THE QUERY
// var_dump($res);


// CONSTRUCT THE INSERT QUERY
$xdate = date('Y-m-d H:i:s');
$sql = "INSERT INTO my_table ( xdate ) VALUES ( '$xdate' )";

// RUN THE QUERY TO INSERT THE ROW
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// GET THE AUTO_INCREMENT KEY OF THE RECORD JUST INSERTED
$id  = $mysqli->insert_id;
echo "MySQLI INSERTED A ROW CONTAINING <b>$xdate </b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
echo PHP_EOL;


// SLEEP A BIT
echo PHP_EOL . "SLEEPING...";
sleep(11);


// CONSTRUCT THE UPDATE QUERY
$xdate = date('Y-m-d H:i:s');
$sql = "UPDATE my_table SET xdate = '$xdate' WHERE id='$id' LIMIT 1";
$res = $mysqli->query($sql);
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// SELECT THE NEW VALUE
$sql = "SELECT * FROM my_table";
$res = $mysqli->query($sql);
$row = $res->fetch_object();
print_r($row);

Select allOpen in new window

As others pointed out all your question includes is assigning date to a variable, $now,
which can also be achieved when running the query and using now() as the value.

Apologies, I only posted that code because I thought that was the problem. but, I removed the DATETIME part and the update query executed without a problem. I put the DATETIME back in and nothing happens. There is no error on the page itself, it redirects as it should if everything worked properly.

             

                   $cid = filter_var($_GET['cid'], FILTER_SANITIZE_URL);
			$status = "Answered";
			$now = date('Y-m-d H:i:s');
			$stmt = $link->prepare("UPDATE `corrective_actions` SET `immediate_action` = ?, `root_cause` = ?, `preventive_measures` = ?, `status` = ?, `dt_answered` = ? WHERE `id` = ? AND `to_user` = ? LIMIT 1");
			$stmt->bind_param("ssssiss", $_POST['immediate_action'], $_POST['root_cause'], $_POST['preventive_measures'], $status, $cid, $_SESSION['user_details']->safe_id, $now);
			$stmt->execute();
			$stmt->close();
			header("location: dashboard.php");
			exit();

Select allOpen in new window

CREATE TABLE `corrective_actions` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `ca_date` date NOT NULL,
 `from_user` varchar(255) NOT NULL,
 `to_user` varchar(255) NOT NULL,
 `ca_cat` int(11) NOT NULL,
 `level` int(11) NOT NULL,
 `ca_reason` text NOT NULL,
 `immediate_action` text NOT NULL,
 `root_cause` text NOT NULL,
 `preventive_measures` text NOT NULL,
 `status` varchar(255) NOT NULL DEFAULT 'Pending',
 `dt_answered` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

Select allOpen in new window

@ arnold, I just had a quick look and it doesn't seem like you can use bind_param with NOW(). I could be wrong but that was what a quick Google search told me. Also, is there a chance that the times could be different because one is from php server and the other mysql server if they were perhaps running on different servers?

I don't know why, but I redid it and it now works. I am still trying to play spot the difference.

The new one:

$stmt = $link->prepare("UPDATE `corrective_actions` SET `dt_answered` = ?,
`immediate_action` = ?,
`root_cause` = ?,
`preventive_measures` = ?,
`status` = ?
WHERE `id` = ?
AND `to_user` = ?
LIMIT 1");
$stmt->bind_param("sssssis",
$now,
$_POST['immediate_action'],
$_POST['root_cause'],
$_POST['preventive_measures'],
$status,
$cid,
$_SESSION['user_details']->safe_id);

Select allOpen in new window

ORIGINAL

$stmt = $link->prepare("UPDATE `corrective_actions` SET `immediate_action` = ?,
`root_cause` = ?,
`preventive_measures` = ?,
`status` = ?,
`dt_answered` = ?
WHERE `id` = ?
AND `to_user` = ?
LIMIT 1");
$stmt->bind_param("ssssiss",
$_POST['immediate_action'],
$_POST['root_cause'],
$_POST['preventive_measures'],
$status,
$cid,
$_SESSION['user_details']->safe_id,
$now);

Select allOpen in new window

Ahhh.

AND `to_user` = {$now}

Select allOpen in new window

How silly of me! Sorry for wasting everyone's time on this when I should just be more careful!

Sorry for wasting everyone's time

Find me a developer that has not had at least 100 incidents of a bug due to not seeing what was in front of them and I will show you the Easter Bunny

Hahaha! :-)

Have to choose one "best" answer again. Think I know whose turn it is but will double check..

is there a chance that the times could be different because one is from php server and the other mysql server...

Although it's not relevant to the issue here, which was an undiscovered query failure, the answer to that question is "Yes, the times of the servers are completely independent."  See PHP and MySQL Have Different Time Zone Settings in this article:
https://www.experts-exchange.com/articles/20920/Handling-Time-and-Date-in-PHP-and-MySQL-OOP-Version.html

One thing you should not directly pass form input to your SQL to avoid the possibility of SQL injection.
You shoukd mysql_real_escape.... To get the data out of the form field into a variable.