Invalid datetime format: 1366 Incorrect string value

Question

I'm getting this error:

SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xBD Inch...' for column 'column-name' at row 1

My database, table, and column have the format utf8mb4_unicode_ci also column-name is type text and NULL.

This is the value of the column-name

[column-name] => Some text before 11 ▒ and other text after, and after.

However I wait that laravel adds quotes to column's values, because the values are separated by commas (,). It should be as follow:

[column-name] => 'Some text before 11 ▒ and other text after, and after.'

See below the Schema

    Schema::create('mws_orders', function (Blueprint $table) {
        $table->string('custom-id');
        $table->string('name');
        $table->string('description')->nullable();
        $table->string('comment')->nullable();
        $table->integer('count')->nullable();
        $table->text('column-name')->nullable();
        $table->timestamps();

        $table->primary('custom-id');
    });

I have been looking for on google but not any solution, yet.

Anyone has an idea how to solve this issue?

I'm using Laravel 5.5 and MariaDB 10.2.11.

Looks like the column is supposed to be date time format, not a string. check your schema. — aynber
The problem looks like other, Laravel is not enclosing into quotes. There are commas in my string. The insert error shows column's names with backtick, but column's values don't have quotes. — gvd
Laravel uses parameter binding, which does not quote the values. Show your database schema and the exact code you're using. — aynber
I'm using instance of my Model to insert $myModel = new MyModel(); $myModel->insert($data); when you talk about schema, do you refer to migration? — gvd

gvd gvd · Accepted Answer · 2018-01-17T21:09:33

I solved it, encoding to uft-8 all string columns that generated this error before insert. For example, the column that generated the error was column-name, I encoded as show bellow. Also I found other column with the same error, I used this solution, too.

$data [
//key=>values 
];

$myModel = new MyModel(); 

$data['column-name'] = DB::connection()->getPdo()->quote(utf8_encode($data['column-name']));

$myModel->insert($data);

Invalid datetime format: 1366 Incorrect string value

6 Answers