Can't create mysql table due to Incorrect date

Question

The following query works

select date(str_to_date("08-Nov-2005 22:07","%d-%M-%Y %H:%i:%S"))

As expected, it returns

2005-11-08

The following query also works

select date(str_to_date("XXXX","%d-%M-%Y %H:%i:%S"))

As expected, it returns

NULL

I should note there is a warning, but it doesn't stop the query from executing and returning a NULL result

show warnings

yields

But the problem occurs when I try to create a table from the result.

This works

CREATE TABLE myTable AS select date(str_to_date("08-Nov-2005 22:07","%d-%M-%Y %H:%i:%S"))

But this does not

CREATE TABLE myTable AS select date(str_to_date("XXXX","%d-%M-%Y %H:%i:%S"))

Error message is

Incorrect datetime value: 'XXXX' for function str_to_date

This is a very simple toy example, but I am trying to create a much larger table with many date values correctly parsed and it has the same effect.

What am I doing wrong?

Answer 1

The behavior you see implies to me that you are running in a strict SQL_MODE (which is a good idea in general BTW).

You may be able to accomplish what you want by setting a less strict SQL_MODE for your session.

Here's an example showing your CREATE TABLE statement failing in MySQL 5.7 with STRICT_ALL_TABLES mode, but succeeding once I remove that restriction:

mysql> select @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
| STRICT_ALL_TABLES  |
+--------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE myTable AS select date(str_to_date("XXXX","%d-%M-%Y %H:%i:%S"));
ERROR 1411 (HY000): Incorrect datetime value: 'XXXX' for function str_to_date

mysql> set session sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE myTable AS select date(str_to_date("XXXX","%d-%M-%Y %H:%i:%S"));
Query OK, 1 row affected, 1 warning (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> select * from myTable;
+-----------------------------------------------+
| date(str_to_date("XXXX","%d-%M-%Y %H:%i:%S")) |
+-----------------------------------------------+
| NULL                                          |
+-----------------------------------------------+
1 row in set (0.01 sec)

Answer 2

Well tried both the queries you stated above and seems to be working fine for me. Only thing is value inserted in 2nd query is NULL which is what you said you expect.