mysql8: Incorrect datetime value: '2016-02', what can I do?

For example, there are two tables like this:

1.original table

2.new table(Created based on the original table, an additional is date field is added)

date_str in the original table should be in date format, but the actual field type is varchar, and the data stored is also a variety of, not only date format strings, but also other formats (for example, 123, abc, etc.).
I will now extract the date_str field from the original table and insert it into the new table. Check whether it is in date format when inserting, and if not, fill DATE_FORMAT in the is_date field

My SQL statement is:

INSERT INTO new_table ( date_str, is_date ) 
SELECT
ot.date_str  AS date_str,
CASE	
	WHEN DATE_FORMAT( ot.date_str, '%Y-%m-%d' ) IS NULL 
	THEN 'DATE_FORMAT' 
END AS is_date 
FROM original_table ot

If you execute this SQL, you will get an error: Incorrect DateTime value: '2016-02'

I looked it up on the Internet and needed to adjust the MySQL configuration file my.cnf to remove the sql_mode=STRICT_TRANS_TABLES from the configuration file, but I didn't want to do so, because it would affect other tables.

Use STR_TO_DATE instead of DATE_FORMAT. That method parses strings into dates and returns NULL if the string can't be parsed.

SELECT 
    IF(STR_TO_DATE( date_str, '%Y-%m-%d' ) IS NULL,1,0) as is_date
FROM table_name

Dates in all databases except SQLite are binary values, they have no format. Most databases will parse strings into dates implicitly though. If the parse fails, the entire query fails.

DATE_FORMAT itself expects a date value to format into a string.

The only reason the query works at all is that MySQL implicitly parses the string into a date before passing it to DATE_FORMAT. When the implicit cast fails, the entire query fails. At that point, DATE_FORMAT hasn't been called yet.

This example, also shown in this dbFiddle, creates a table with some text fields and calculates the is_date fields.

CREATE TABLE MyTable(
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    date_str varchar(50),
    is_date boolean null
);
INSERT INTO MyTable(date_str)
VALUES
('2016-02-16'),
('2016-02'),
('Banana');

A query with STR_TO_DATE returns the following result

SELECT ID,IF(STR_TO_DATE(date_str,'%Y-%m-%d') is null,0,1) as is_date
FROM MyTable;

------
ID	is_date
1	1
2	0
3	0

To update the table we need to use IGNORE. STR_TO_DATE generates a warning if the value doesn't match. SELECT doesn't care but UPDATE/INSERT will convert the warning to an error :

UPDATE IGNORE MyTable     
    Set MyTable.is_date=IF(STR_TO_DATE(date_str,'%Y-%m-%d'),0,1)
;

SELECT * 
FROM MyTable;
-----

ID	date_str	is_date
1	2016-02-16	0
2	2016-02	    1
3	Banana	    1

You can temporarily set the sql_model attribute of the mysql configuration file my.cnf, remove STRICT_TRANS_TABLES, and then add it back after the sql is executed. The sql is:

set sql_mode='ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

INSERT INTO new_talbe ( date_str, is_date ) 
SELECT
ot.date_str AS date_str,
CASE    
    WHEN DATE_FORMAT( ot.date_str, '%Y-%m-%d' ) IS NULL 
    THEN 'DATE_FORMAT' 
END AS is_date 
FROM original_table ot;

set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

Or use regular expressions.