How to solve Symfony 6 exception occurred while executing a query: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '00-00-00 00:00:00' for column

This exception shows up when you're trying to update the schema of your database with new properties and you're trying to add a non-nullable new datetime column and there's already data in your table.

In theory, this is caused because the MySQL engine has the strict mode enabled and therefore it does not permit inserting 0000-00-00 or 0000-00-00 00:00:00 as a valid date/Datetime value in your tables. The solution for this problem will depend on what you're trying to achieve, the environment where your project runs, and whether your project is being created from scratch or has been up and running for a while at least.

A. Fix your database schema to be compatible with the old one

If it is the first time that you have seen this issue in your project because you're building it from scratch, then you're probably simply having confusion with a schema that you created previously, but you're adding a new column to your table, where previous records were inserted and they don't match the requirements of the updated schema.

But what the hell does that mean? Let me explain that to you with a straightforward example. Consider the following entity that you created first, it's a simple entity with 2 columns:

• id (big int, not nullable)
• details (varchar255, not nullable)

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity()]
class Example
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\Column(length: 255)]
    private ?string $details = null;

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getDetails(): ?string
    {
        return $this->details;
    }

    public function setDetails(string $details): static
    {
        $this->details = $details;

        return $this;
    }
}

You built the schema using php bin/console doctrine:schema:update --force --complete and that's it, the schema was built successfully and now you have the table and you even persisted one record:

Excellent! The problem that you googled for will appear now if we proceed to the following thing and have done the previous steps of creating a schema and persisting at least one record. Now you decide to create a new column in your table that allows you to insert the date and time when the record was created:

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity()]
class Example
{
    // ... //

    #[ORM\Column(type: Types::DATETIME_MUTABLE)]
    private ?\DateTimeInterface $created_at = null;

    public function getCreatedAt(): ?\DateTimeInterface
    {
        return $this->created_at;
    }

    public function setCreatedAt(?\DateTimeInterface $created_at): static
    {
        $this->created_at = $created_at;

        return $this;
    }

    // ... //
}

Now you build the schema again php bin/console doctrine:schema:update --force --complete but this time, the exception appears, showing the mentioned exception. SQLSTATE[22007]: Invalid DateTime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'created_at' at row 1.

As we already had 1 record (This is my first example), when updating the schema of the table, the existing record will have the created_at column just like any other record. But, as the created_at field is not nullable, the engine will try to persist the record anyway using the 0000-00-00 00:00:00 value, which according to the configuration of your MySQL engine (using strict mode), is not allowed.

So in this case, as you had previous records in your database, you may either delete them (if you're working locally from scratch on your project), then rebuild the model and it will work now. Or, you may simply keep the old records in your database but mark the column as a nullable field (created_at can be null) in your entity and rebuild the schema:

#[ORM\Column(type: Types::DATETIME_MUTABLE, nullable: true)]
private ?\DateTimeInterface $created_at = null;

The solution works not only for Symfony but for any other PHP framework that follows a similar logic just like Laravel, and CodeIgniter. 

B. Allowing insertions of ZERO DATES in your database engine

On the other side, if you're working on an existing project where the original design contemplated the insertion of ZERO DATES (0000-00-00 00:00:00) and did allow that in the past, then you may simply stick to this behavior by simply modifying the setting in your MySQL engine configuration.

The default SQL mode in MySQL includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTION. So if you want to allow the insertion of ZERO DATES in your project for those rows where the datetime column cannot be null but it needs to have a date, be sure to disable that mode by removing it from the sql-mode string in your configuration file:

[mysql]
# You need to remove the NO_ZERO_DATE from your SQL mode to allow the insertion of zero dates!
#sql-mode="NO_ZERO_DATE"

As the exception itself is related to MySQL, this solution will work for any other framework that you're using independently from the programming language that you're using as long as you're using MySQL as the database engine.

Happy coding ❤️!