Why this query does not work in mysql :

 CREATE TABLE IF NOT EXISTS __temp_orders ( SELECT * FROM orders WHERE STR_TO_DATE(orders.data,'%d.%m.%Y') < '2016-11-01 09:35:00' )

Writes:

[HY000] [1411] Incorrect datetime value: '' for function str_to_date

Although a separate subquery:

 SELECT * FROM orders WHERE STR_TO_DATE(orders.data,'%d.%m.%Y') < '2016-11-01 09:35:00'

works fine and returns what I want to create a table from.

  • Maybe temp_orders exists and the corresponding field in it is of type datetime, and then you choose a string from the database ... maybe you need to explicitly list the required fields instead of * , including the use of str_to_date ... - Mike

1 answer 1

When using my interface, something like LIMIT 0.100 is implicitly added to the second request (so that everything does not appear on the screen), and the processing of the orders table does not reach records with an empty data field, which leads to an error in the case of CREATE TABLE.

Added to the condition data! = '' And everything works with a bang!

CREATE TABLE IF NOT EXISTS __temp_orders (SELECT * FROM orders WHERE data! = '' AND STR_TO_DATE (data, '% d.% M.% Y') <'2016-11-01 09:35:00');