MySQL rejects a date or time value with error 1292
Database Error Number: 1292Short answer
Error 1292 means MySQL refused a date or time value because strict mode is on and the value is not valid for the column. Empty strings, zero dates and impossible calendar dates are the usual ones. Send a real date in YYYY-MM-DD HH:MM:SS form, or NULL, instead of changing the SQL mode.
Fix it fast
1Find out which cause you have
Read the SQL mode of the server and the column definition. Strict mode plus a bad value is what produces the error.
SELECT @@SESSION.sql_mode; SHOW CREATE TABLE orders\G2Match what you see, then jump to its fix
- 1
The value in the error is '' or '0000-00-00 00:00:00'An empty string or a zero date is sent for the columnFix - 2
The value looks normal but has a day that does not exist, such as 2004-04-31The date does not exist on the calendarFix - 3
The value in the error has a colon-separated or otherwise unusual shape, such as 10:45:15 sent to a date columnThe string is in a format MySQL reads differentlyFix
- 1
What does the error look like?
Database Error Number: 1292Incorrect datetime value
Cause 1 of 3
An empty string or a zero date is sent for the column
1Check
Form fields left blank arrive as empty strings. With strict mode and NO_ZERO_DATE both enabled, '0000-00-00' is not permitted and the insert fails.
SELECT @@SESSION.sql_mode LIKE '%STRICT%' AS strict, @@SESSION.sql_mode LIKE '%NO_ZERO_DATE%' AS no_zero;2Fix
Send NULL when there is no date (and make the column nullable), or a real date when there is one.
$shipped = $_POST['shipped_at'] !== '' ? $_POST['shipped_at'] : null;
$stmt = $db->prepare('INSERT INTO orders (shipped_at) VALUES (?)');
$stmt->bind_param('s', $shipped);
$stmt->execute();3Verify
Insert once with an empty field and once with a date.
SELECT id, shipped_at FROM orders ORDER BY id DESC LIMIT 2;Confirmed by dev.mysql.com
Cause 2 of 3
The date does not exist on the calendar
1Check
MySQL requires valid month and day values, not only numbers in range 1 to 12 and 1 to 31. With strict mode on, an invalid date is an error rather than a silent zero date.
SELECT STR_TO_DATE('2004-04-31', '%Y-%m-%d');2Fix
Validate the date in PHP before the query, and fix the code that builds it (month and day swapped is the common mistake).
$d = DateTime::createFromFormat('Y-m-d H:i:s', $input);
$ok = $d && $d->format('Y-m-d H:i:s') === $input;3Verify
Insert a known-good value.
INSERT INTO orders (shipped_at) VALUES ('2026-11-17 10:30:00');Confirmed by dev.mysql.com
Cause 3 of 3
The string is in a format MySQL reads differently
1Check
MySQL accepts relaxed delimiters between the parts of a string value, but reads the parts in year-first order. For example '10:45:15' in date context is read as year 2010, month 45, and fails.
SELECT CAST('10:45:15' AS DATE);2Fix
Convert the value to YYYY-MM-DD HH:MM:SS in PHP before binding it, for example from a day-first form field.
$d = DateTime::createFromFormat('d/m/Y H:i', '17/11/2026 10:30');
$value = $d->format('Y-m-d H:i:s');3Verify
Insert the converted value and read it back.
SELECT shipped_at FROM orders ORDER BY id DESC LIMIT 1;Confirmed by dev.mysql.com
How do I stop it coming back?
- Make optional date columns nullable and send NULL for 'no date'.
- Convert and validate dates in the application before they reach SQL.
- Keep the same SQL mode in development and production so errors show early.
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
Back end
CodeIgniter 4: try/catch does not catch a failed insert in the model
DatabaseException
CodeIgniter throws a DatabaseException on a failed query only when DBDebug is true, and inside a transaction it does not throw unless you ask.
3 causes, each with a fixBack end
CodeIgniter 4 where() builds a different condition than you meant
A SQL syntax error that appears only when a value contains a quote character
The query builder joins every where() with AND and every orWhere() with OR, with no brackets.
3 causes, each with a fixBack end
Call to undefined function mysql_pconnect() after a PHP upgrade
Call to undefined function mysql_connect()
The old mysql_* functions belonged to the ext/mysql extension, which PHP removed in version 7.0.
2 causes, each with a fixBack end
CodeIgniter cannot connect to the MySQL database server
Unable to connect to your database server using the provided settings.
CodeIgniter only reports that its connection failed.
4 causes, each with a fixBack end
Laravel insert fails with 'Field user_id doesn't have a default value'
The insert works when the column is removed or made nullable
Error 1364 means Laravel sent an INSERT without a value for a NOT NULL column that has no default, and MySQL strict mode refuses it.
3 causes, each with a fixBack end
Laravel migration cannot drop a column that has a foreign key
Error on rename of '%s' to '%s' (errno: %d - %s)
MySQL refuses to drop a column while a foreign key constraint still uses it.
2 causes, each with a fix