Invalid datetime format: step 7

Hi, I am migrating from M 1.9.3 to M 2.3.4. For step 7 I always got date time format error for date around Mar 8,9,10 each year.
Actually this happened to other steps too. But for step 7, I had twice already, and each time it involved many tables as listed below:
sales_flat_order
sales_flat_order_grid
sales_flat_order_status_history
sales_flat_order_item
sales_payment_transaction
sales_flat_invoice
sales_flat_invoice_grid
sales_flat_shipment_grid
Each time I had to issue sql statement to correct the mistake, and it takes about 20 minutes to cover all the tables. 
Here are some of the error message. Note, the datetime field is not ‘0000-00-00 00:00:00’, but is actually filled with correct value to my understanding:

  1. [error] [ub_data_migration] CDbCommand failed to execute the SQL statement: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: ‘2014-03-09 02:40:30’ for column ‘order_created_at’
  2. [error] [ub_data_migration] CDbCommand failed to execute the SQL statement: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: ‘2014-03-09 02:40:30’ for column ‘created_at’ at row 1. The SQL statement executed was: INSERT INTO `sales_order` 
  3. [error] [ub_data_migration] CDbCommand failed to execute the SQL statement: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: ‘2014-03-09 02:40:30’ for column ‘created_at’ at row 1. The SQL statement executed was: INSERT INTO `sales_order_grid` 
  4. [error] [ub_data_migration] CDbCommand failed to execute the SQL statement: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: ‘2014-03-09 02:40:31’ for column ‘created_at’ at row 1. The SQL statement executed was: INSERT INTO `sales_invoice` 

I can see all the similar issues with customer_entity migration for step 6 are all related to date near Mar 8,9,or 10 each year. 
Thanks.
Josh
 

1 answer

Profile photo of Mall Staff 184060.00 $tone March 23, 2020
Public

Hi Josh,

I wonder if your MySQL version is 5.7.x. 
 
The issues you mentioned is strange. It seems that it related to the datetime settings in MySQL of your M2’s server only. 
 
You can take a look at the datetime setting here: https://dev.mysql.com/doc/refman/5.7/en/datetime.html

Meanwhile, you should contact your system admin or hosting provider to get further help on that issue. 
 
Regards,
Mall.

#1

Please login or Register to Submit Answer

Written By

Comments