8 - SQL error "String data, right truncated..." when creating/updating a node programmatically, but OK when inserting via the CLI. Why?

Question

When I attempt to programmatically update an event node, I get the error: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'field_event_end_date_and_time_value'...

For most date fields. If I insert the record(s) with the same information into MySQL directly via the CLI, it updates/inserts correctly. Anyone have any idea what's going wrong here?

Full error from logs:

Drupal\Core\Entity\EntityStorageException: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'field_event_end_date_and_time_value' at row 1: INSERT INTO {node__field_event_end_date_and_time} (entity_id, revision_id, bundle, delta, langcode, field_event_end_date_and_time_value) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5); Array ( [:db_insert_placeholder_0] => 7159 [:db_insert_placeholder_1] => 7750 [:db_insert_placeholder_2] => events [:db_insert_placeholder_3] => 0 [:db_insert_placeholder_4] => en [:db_insert_placeholder_5] => 2014-06-18CDT21:30:00 ) in Drupal\Core\Entity\Sql\SqlContentEntityStorage->save() (line 770 of /var/www/acg/html/core/lib/Drupal/Core/Entity/Sql/SqlContentEntityStorage.php).

My queries:

UPDATE node__field_event_end_date_and_time SET field_event_end_date_and_time_value = '2014-06-18CDT21:30:00' WHERE delta = 0 AND langcode = 'en' AND entity_i
d = 7159 AND deleted = 0;

and

INSERT INTO node__field_event_end_date_and_time (entity_id, revision_id, bundle, delta, langcode, field_event_end_date_and_time_value) VALUES(7159, 7750, '
events', 0, 'en', '2014-06-18CDT21:30:00');

work correctly through the CLI.

Output of show columns FROM node__field_event_end_date_and_time

+-------------------------------------+------------------+------+-----+---------+-------+
| Field                               | Type             | Null | Key | Default | Extra |
+-------------------------------------+------------------+------+-----+---------+-------+
| bundle                              | varchar(128)     | NO   | MUL |         |       |
| deleted                             | tinyint(4)       | NO   | PRI | 0       |       |
| entity_id                           | int(10) unsigned | NO   | PRI | NULL    |       |
| revision_id                         | int(10) unsigned | NO   | MUL | NULL    |       |
| langcode                            | varchar(32)      | NO   | PRI |         |       |
| delta                               | int(10) unsigned | NO   | PRI | NULL    |       |
| field_event_end_date_and_time_value | varchar(20)      | NO   | MUL | NULL    |       |
+-------------------------------------+------------------+------+-----+---------+-------+

Does anyone see it?

Answer 1

The CLI works because then you're not in strict mode and it just cuts the string off.

Your date format is wrong. It must be in the format defined by DATETIME_DATETIME_STORAGE_FORMAT and converted to the correct timezone DATETIME_STORAGE_TIMEZONE.