Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

sql - MySQL STR_TO_DATE NULL on error

I'm currently migrating a table with dates in VARCHAR columns to a new table with DATE columns. I managed to sanitize the string values in the old table to the format "YYYY-MM-DD" but when I try to perform the insert I got an error with the date "2006-04-31" because that April only had 30 days (was a typo when it was registered),

My question is: how can I set to NULL the column when the date is invalid without getting an error? My SQL is the following:

INSERT INTO newFancyTable (created_at)
SELECT str_to_date(created, '%Y-%m-%d') FROM oldCrappyTable;

And the error is the following:

Error Code: 1292. Incorrect date value: '2006-04-31' for column 'created_at' at row 1

Thanks

UPDATE

I also tried using the following approach:

INSERT INTO newFancyTable (created_at)
SELECT CAST(created AS DATE) FROM oldCrappyTable;

With the same error, and trying to update the oldCrappyTable would return the same:

UPDATE oldCrappyTable SET created = CAST(created AS DATE);

Both return:

Error Code: 1292. Incorrect datetime value: '2006-04-31'

UPDATE 2

At last, I used multiple CASEs to isolate that invalid dates, in sum they were only 5 of them, Nevertheless, the issue can be reproduced by doing:

CREATE TABLE dates_temp (
  test_date DATE DEFAULT NULL
) ENGINE=MEMORY;

INSERT INTO dates_temp
SELECT STR_TO_DATE("2006-04-31", '%Y-%m-%d');

DROP TABLE dates_temp;
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

A possible workaround is to turn off strict mode, either for the whole server, for a particular session, or for just a few statements. For example:

 set @old_sql_mode = @@sql_mode; 
 set sql_mode = ''; 
 -- Run some statements which may result in error
 set sql_mode = @old_sql_mode;

Additional Info

MySQL Documentation


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...