mysql1411_MySQL Error Code: 1411. Incorrect datetime value: '' for function str_to_date

问题

I have been asked to make changes to an existing application that at this time is only deployed to a Linux Production server. I have gone ahead and have the app for the most part working on my local Windows PC. I have a full copy of the MySQL DB from Production installed on my local PC. The Production DB is MySQL v5.0.95 on Linux and my local DB is MySQL v5.5 on Windows. Both are in InnoDB mode.

My issue is with a statement such as the following. Made generic for ease of use by others wanting to help.

update atable

set adate=DATE_ADD(str_to_date('','%m/%d/%Y'), INTERVAL 0 DAY)

where anum='1'

In some cases an empty string is passed in which in Production does not cause any issues and allows the record to be saved/updated but locally it throws a SQLException. So I tried the SQL statement directly against my local DB and I get the following error message in both cases.

Error Code: 1411. Incorrect datetime value: '' for function

str_to_date

I've looked at the Production my.cnf and my local my.ini looking for any major differences and I have also tried to use the sql-mode "ALLOW_INVALID_DATES" locally but it did not change the end result.

I know that I could change the code to not pass in these empty strings in but there are many statements like this and at this time I do not wish to make changes to all of these if possible. This customer has a limited budget and timeframe and I want to focus on their new requirements. I'm looking for input as to how I can get my local environment working as it does in Production if possible.

Thanks for your time.

回答1:

The SQLException does not come directly from MySQL, it's probably triggered by your client language. MySQL will just generate a warning you can normally ignore. Whatever, the ALLOW_INVALID_DATES SQL mode should actually do the trick:

Warning:

mysql> SET @@SESSION.sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test (date_created) VALUES (str_to_date('','%m/%d/%Y'));

Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;

+---------+------+-------------------------------------------------------+

| Level | Code | Message |

+---------+------+-------------------------------------------------------+

| Warning | 1411 | Incorrect datetime value: '' for function str_to_date |

+---------+------+-------------------------------------------------------+

1 row in set (0.00 sec)

No warning:

mysql> SET @@SESSION.sql_mode='ALLOW_INVALID_DATES';

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test (date_created) VALUES (str_to_date('','%m/%d/%Y'));

Query OK, 1 row affected (0.03 sec)

Edit: If you are looking for a way to rewrite the query, you could try something like this:

update atable

set adate=NULL

where anum='1'

Of course, this requires that adate is nullable.

回答2:

I was getting the same 1411 error when trying to load data which has some blank values for dates:

CLM_FROM_DT is a DATE

LOAD DATA INFILE 'Sample_1.csv'

INTO TABLE INPATIENT

FIELDS TERMINATED BY ','

LINES TERMINATED BY '\n'

IGNORE 1 LINES

(DESYNPUF_ID,

@CLM_FROM_DT)

SET CLM_FROM_DT = STR_TO_DATE(@CLM_FROM_DT, '%Y%m%d')

Awhile back I had tried ALLOW_INVALID_DATES with MySQL v.5 or so, and I don't think it worked. Now I'm on MySQL 8.0 and when I set ALLOW_INVALID_DATES in the mysql terminal session, it works. So, it seems ALLOW_INVALID_DATES is touchy.

I'm running Mac OS 10.11.6

来源：https://stackoverflow.com/questions/14961490/mysql-error-code-1411-incorrect-datetime-value-for-function-str-to-date