Can't create mysql table due to Incorrect datetime value "..." for function str_to_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?

score 3 · Accepted Answer · answered Oct 28 '16 at 19:23

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)

Thank you! This was the issue for me – kane Oct 28 '16 at 19:42 — kane, Oct 28 '16 at 19:42
Yes...was the issue for me too. Thanks ! – daticon Nov 20 '17 at 13:48 — daticon, Nov 20 '17 at 13:48

score 0 · Answer 2 · answered Oct 28 '16 at 19:18

0

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.

answered Oct 28 '16 at 19:18

Just_Do_It

765
6
18

I wonder if it has to do with system settings or suppressing warnings. I'm on a mac OSX with MySQL 5.6 – kane Oct 28 '16 at 19:22

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

2 Answers2

Linked