Search code examples
mysqldatetimestr-to-date

Can't create mysql table due to Incorrect datetime value "..." for function str_to_date

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

enter image description here

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?

Solution

  • 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)