Why would SQL update the date into tabel to 0000-00-00 00:00:00?

I am trying to update the field SEEN in a SQL table to the current time and date.

Here is the code:

$now=date('d-m-Y H:i:s',time());
$query="UPDATE mytable SET SEEN = '".$now."' WHERE ID_ITEM = ".$id_material;
$stmt=$dbh1->prepare($query);
$query ;
$stmt->execute();

It sets my SEEN field to 0000-00-00 00:00:00. If I write a specific date directly into the query, say “2021-03-10 02:30:00” it would write that date into the SEEN field. But instead of $now, it would output 0s. And $now is fine, it outputs the correct timestamp.

Answer

The format you are using is not the same that MySQL DATETIME uses:

$now = (new DateTime("now"))->format("Y-m-d H:i:s");

Advertisement

Answer