Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Node.js, PHP and APIs

Laravel insert fails with Invalid datetime format 1292 Incorrect datetime value

ErrorSQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'created_at' at row 1

Short answer

MySQL ran in strict mode and refused a date or time value that it cannot store. The usual culprits are a zero date such as 0000-00-00, a date that does not exist (month 13, 31 April), or a TIMESTAMP outside 1970 to 2038. Find the exact value in the error, then correct the data or make the column nullable.

  • Applies to MySQL 8.4, Laravel 12, PHP 8.3
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    The error prints the value and the column name. Read the SQL mode of the connection, then look at that value against the three causes below.

    SELECT @@SESSION.sql_mode;
  2. 2Match what you see, then jump to its fix

    1. 1Incorrect datetime value: '0000-00-00 00:00:00'A zero date is sent while NO_ZERO_DATE and strict mode are onFix
    2. 2The value in the message has a month above 12 or a day the month does not have, for example 2025-04-31 or 2025-13-02.The date does not exist on the calendarFix
    3. 3The column is a TIMESTAMP and the value is before 1970 or after 19 January 2038.A TIMESTAMP column receives a date outside its rangeFix

What does the error look like?

  • SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'created_at' at row 1
  • SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value

Cause 1 of 3

A zero date is sent while NO_ZERO_DATE and strict mode are on

1Check

MySQL 8.4 enables strict mode and NO_ZERO_DATE together by default, and with both on a zero date is an error. Laravel's mysql connection also sets strict to true. Rows written long ago by an older server can still contain zero dates, and an update that copies them back fails.

SELECT @@SESSION.sql_mode;
SELECT id FROM orders WHERE shipped_at = '0000-00-00 00:00:00' LIMIT 5;

2Fix

Store NULL for a missing date instead of a zero date: make the column nullable and clean up the old rows. Do not turn strict mode off just to hide it.

// migration
$table->dateTime('shipped_at')->nullable()->change();

-- then clean existing rows
UPDATE orders SET shipped_at = NULL WHERE shipped_at = '0000-00-00 00:00:00';

3Verify

No zero dates remain and the insert works.

SELECT COUNT(*) FROM orders WHERE shipped_at = '0000-00-00 00:00:00';

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

The date does not exist on the calendar

1Check

The server requires month and day to be valid, not just inside 1 to 12 and 1 to 31. With strict mode on, an impossible date is an error; with it off, the date would be silently turned into a zero date. This typically comes from parsing a day-first string such as 31/04/2025 as month-first.

SHOW COLUMNS FROM orders LIKE 'shipped_at';

2Fix

Parse the incoming string with the format it was written in, validate it, and pass a real date object or a Y-m-d H:i:s string to the query.

$date = \DateTimeImmutable::createFromFormat('d/m/Y', $request->input('date'));
Order::create(['shipped_at' => $date]);

3Verify

The insert succeeds and the stored value is the date you sent.

SELECT shipped_at FROM orders ORDER BY id DESC LIMIT 1;

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

A TIMESTAMP column receives a date outside its range

1Check

TIMESTAMP only covers 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC, while DATETIME covers the years 1000 to 9999. TIMESTAMP columns also always require a valid date.

SHOW COLUMNS FROM subscriptions LIKE 'ends_at';

2Fix

Change the column to DATETIME when you need dates past 2038 (or before 1970).

// migration
$table->dateTime('ends_at')->nullable()->change();

3Verify

A far-future date now saves.

php artisan migrate:status

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Use nullable date columns for 'not set yet' instead of zero dates or empty strings.
  • Parse and check dates in your own code before they reach the database.
  • Keep strict mode on in every environment so bad data fails in development, not in production.
  • Choose DATETIME for anything that can pass the year 2038.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Back end

php artisan migrate stops with could not find driver

could not find driver (Connection: mysql, SQL: select * from information_schema.tables ...)

Laravel talks to the database through a PDO driver, and this error means the PHP that runs artisan has none for your DB_CONNECTION.

3 causes, each with a fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.