SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: 'column_name' in Laravel

SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: 'column_name' in Laravel

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Decoding SQLSTATE[22007] in Laravel: The Nullable Trap

When debugging database insertions in Laravel, you might encounter an error that seems contradictory at first glance. You see SQLSTATE[22007]: Invalid datetime format, but the underlying message is 1366 Incorrect integer value. This specific combination—often appearing when inserting into a table like product_prices with columns such as created_at, product_id, and unit_id—usually points to a mismatch between your database schema definition and how Laravel interprets nullable fields.

Consider a scenario where you are bulk-inserting data. Your SQL query looks something like this:

insert into product_prices (created_at, product_id, unit_id, updated_at) values 
(2016-12-06 06:56:01, 27, 1, 2016-12-06 06:56:01), 
(2016-12-06 06:56:01, 27, , 2016-12-06 06:56:01)

The error log indicates that the failure occurred at row 2, where the unit_id value is empty. You might have defined this column in your migration or model as nullable using nullable() to allow for optional values. However, Laravel’s Eloquent ORM and its underlying query builder handle empty strings differently depending on how the database expects nulls to be represented.

The core issue lies in how the database engine interprets an empty string versus a true null. In MySQL, which is the default database for many Laravel installations, an empty string ('') is not the same as NULL. When you pass an empty value from your application layer, it often translates to an empty string in the SQL query. If your column is defined as INT (integer type) but receives an empty string, MySQL throws error 1366: Incorrect integer value: ''.

The confusing part of the error message is the SQLSTATE[22007]: Invalid datetime format prefix. This happens because the database driver sometimes groups certain data conversion errors under a generic "invalid format" category in its exception stack trace, even if the specific root cause is an integer conversion failure. The real culprit is the type mismatch on the unit_id column.

To resolve this without changing your application logic to always pass integers, you need to adjust how the database schema handles the nullability of that integer column. Simply adding nullable() to a column in an Eloquent model or using $table->integer('unit_id')->nullable() in a migration does not automatically allow empty strings; it only allows NULL.

The correct approach is to define the column as nullable at the database level while ensuring your data source provides either a valid integer or a true null. In a raw migration file, this looks like:

$table->integer('unit_id')->nullable();

However, if you are using Laravel's mass assignment with an empty string coming from a request or a bulk insert array, you must ensure that the value is explicitly converted to null when it is missing. You can do this by modifying your model's $fillable array and utilizing the $attributes attribute casting or handling the data before insertion:

protected $casts = [
    'unit_id' => 'integer', // Ensures strict type handling
];

Alternatively, if you must accept empty strings from an external source but store them as nulls in the database, you can use a mutator or a custom fillable setter to convert empty strings to null before saving:

public function setUnitIdAttribute($value)
{
    return $value === '' ? null : $value;
}

This ensures that when the query builder generates the SQL, it outputs NULL instead of an empty string. This distinction is critical because MySQL strictly enforces type safety for integer columns. If you define a column as int, passing ' ' or '' will always trigger the 1366 error unless the database schema explicitly allows strings (which defeats the purpose of an integer field) or the application logic converts the input correctly.

Understanding this behavior prevents subtle bugs in production where bulk inserts fail silently or crash unexpectedly. Always verify that your data types align with how the database engine interprets NULL versus empty strings, especially when working with integer columns marked as nullable.

Our network