<p>MySQL raises Error 1367 when a literal value cannot be coerced into the target column or variable data type during SQL parsing.</p>
<p>MySQL Error 1367: ER_ILLEGAL_VALUE_FOR_TYPE appears when the server meets a date, time, numeric, or enum value that does not match the target data type. Validate and cast the literal or change the column definition to resolve the issue.</p>
Illegal %s '%s' value found during parsing
MySQL throws Error 1367 with message Illegal %s '%s' value found during parsing when it encounters a literal that cannot be converted to the expected data type. The server halts statement execution and returns SQLSTATE 22007.
The error protects data integrity by blocking inserts, updates, or casts that would otherwise introduce invalid or truncated values.
The parser checks each supplied literal against the column or variable type. If the value is out of range, badly formatted, or not listed in an ENUM set, MySQL reports Error 1367 instead of silently modifying the data.
The most common triggers involve malformed date and time strings, numeric overflow, and enum or set members that are not defined in the schema.
First confirm the target column type with DESCRIBE or SHOW COLUMNS. Validate the literal matches the accepted format and range. If needed, CAST the value or alter the column definition.
Run the statement again after correcting the value, or enable strict SQL mode adjustments only when you are certain of consequences.
Invalid date 2024-02-31 in an INSERT will fail; switch to 2024-02-29 or change the column to VARCHAR if you must store free-form text.
Attempting to insert 999999 into a TINYINT column triggers the error; use a larger integer type or reduce the value.
Always validate user input, use parameterized queries, and enable SQL_MODE=STRICT_ALL_TABLES in development to surface issues early.
Define ENUM and SET lists carefully and provide fallback handling in application code when an unsupported option is received.
Error 1292 Truncated Incorrect Value arises for similar data coercion problems. Unlike 1367, it often occurs in non-strict mode when values are truncated. Enabling STRICT mode promotes it to 1367, so fixes overlap.
A date like '2024-13-01' or a time '25:00:00' exceeds valid ranges, leading to error 1367.
Attempting to store 100000 into a TINYINT column exceeds its signed range of -128 to 127.
Inserting 'purple' into an ENUM('red','green','blue') column fails because 'purple' is not an allowed value.
A DATETIME(3) column rejects a value with six microsecond digits unless precision matches.
Occurs in non-strict mode when MySQL silently truncates invalid input.
Raised when character sets reject the supplied bytes.
Similar to 1367 but focuses on numeric range overflow.
Error 1367 can appear in both strict and non-strict modes, but strict mode surfaces additional cases that would otherwise be truncated.
You can disable strict mode, but this risks silent data loss. It is safer to validate and correct data or expand column types.
The first %s is the data type MySQL expected; the second %s shows the actual invalid value encountered.
Galaxy's SQL editor validates literals with AI copilot suggestions and highlights mismatched data types before you run the query, reducing runtime errors.
.avif)
.avif){kind=logo}
