Saving an empty date from PHP into a MySQL DATETIME column fails
The insert fails with an error about an invalid datetime value when the date field is emptyShort answer
An empty string is not NULL. In MySQL's default strict mode a '' in a DATETIME column is an invalid value and the insert fails. The column must also allow NULL. Change the column if needed, and send the SQL value NULL when the form field is empty.
Fix it fast
1Find out which cause you have
Check whether the column accepts NULL, and what SQL mode the server uses.
SHOW COLUMNS FROM my_table LIKE 'published_at'; SELECT @@sql_mode;2Match what you see, then jump to its fix
- 1
SHOW COLUMNS shows NO in the Null columnThe column is declared NOT NULLFix - 2
The query shows '' where the date should bePHP sends an empty string instead of NULLFix - 3
The error mentions '0000-00-00 00:00:00'A zero date is rejected in strict modeFix - 4
A query seems to prove that no NULL values existLooking for NULL rows with = NULLFix
- 1
What does the error look like?
The insert fails with an error about an invalid datetime value when the date field is emptyThe column holds 0000-00-00 00:00:00 instead of NULL
Cause 1 of 4
The column is declared NOT NULL
1Check
In strict mode, a missing value for a NOT NULL column with no default is an error. For a NULL column, NULL is inserted when the value is missing.
SHOW COLUMNS FROM my_table LIKE 'published_at';2Fix
Redefine the column so it allows NULL. MODIFY replaces the whole definition, so include every attribute the column should keep.
ALTER TABLE my_table MODIFY published_at DATETIME NULL DEFAULT NULL;3Verify
Insert a row with NULL.
INSERT INTO my_table (published_at) VALUES (NULL);Confirmed by dev.mysql.com
Cause 2 of 4
PHP sends an empty string instead of NULL
1Check
NULL and the empty string are different values. Strict mode treats a value of the wrong type for the column as invalid, and with strict mode off an invalid DATETIME turns into the zero value.
SELECT published_at IS NULL, published_at = '' FROM my_table LIMIT 5;2Fix
Turn an empty field into the SQL keyword NULL. A SQL-side way is NULLIF, which returns NULL when both arguments are equal.
-- in the statement; :d is your bound value
INSERT INTO my_table (published_at) VALUES (NULLIF(:d, ''));3Verify
The stored value should read back as NULL.
SELECT id FROM my_table WHERE published_at IS NULL;Confirmed by dev.mysql.com
Cause 3 of 4
A zero date is rejected in strict mode
1Check
MySQL 8.4's default SQL mode includes STRICT_TRANS_TABLES and NO_ZERO_DATE. With both on, '0000-00-00' is not permitted and an insert produces an error.
SELECT @@sql_mode;2Fix
Do not store zero dates. Store NULL for unknown dates. Do not turn off strict mode just to make the error go away.
UPDATE my_table SET published_at = NULL WHERE published_at = '0000-00-00 00:00:00';3Verify
Run the previously failing insert again.
SELECT COUNT(*) FROM my_table WHERE published_at IS NULL;Confirmed by dev.mysql.com
Cause 4 of 4
Looking for NULL rows with = NULL
1Check
expr = NULL is never true. Only IS NULL finds NULL values, and = '' finds an empty string.
SELECT * FROM my_table WHERE published_at = NULL;2Fix
Use IS NULL or IS NOT NULL.
SELECT * FROM my_table WHERE published_at IS NULL;3Verify
The rows you inserted with NULL are listed.
SELECT COUNT(*) FROM my_table WHERE published_at IS NULL;Confirmed by dev.mysql.com
How do I stop it coming back?
- Decide per column whether it is NULL or NOT NULL and use a prepared statement that can send NULL.
- Keep strict mode on; fix data instead of relaxing the server.
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Laravel bulk insert fails with 'too many placeholders'
SQLSTATE[HY000]: General error: 1390 Prepared statement contains too many placeholders
Laravel sends one placeholder per value, so one insert of many rows times many columns, or a whereIn with a huge list, creates a statement MySQL refuses.
3 causes, each with a fixDatabases
Laravel query fails: conversion from one collation into another is impossible
SQLSTATE[HY000]: General error: 3988 Conversion from collation utf8_unicode_ci into utf8mb4_0900_ai_ci impossible for parameter
MySQL is comparing or binding a value whose character set cannot be converted into the one on the other side.
3 causes, each with a fixDatabases
PHP cannot connect to MySQL: the connection attempt failed
Error 2003
Error 2003 means the TCP connection to MySQL never opened.
4 causes, each with a fixDatabases
The client cannot reach the MySQL or MariaDB server through its Unix socket file
ERROR 2002 (HY000): Can't connect to local server through socket '/run/mysqld/mysqld.sock' (2) (MariaDB client wording)
Error 2002 means the client tried the Unix socket file named in the message and found nothing it could talk to.
4 causes, each with a fixDatabases
MySQL or MariaDB: Column count of mysql.proc is wrong, table probably corrupted
Error 1558: Column count of mysql.proc is wrong
The mysql.proc system table has a different number of columns than the running server expects.
3 causes, each with a fixDatabases
Laravel migration fails on managed MySQL because a table has no primary key
Add a primary key to the table or unset this variable to avoid this message.
The server has sql_require_primary_key switched on, so every CREATE TABLE or ALTER TABLE must leave the table with a primary key.
3 causes, each with a fix