Description:
In MySQL8.0.16(Windows), SELECT statement is failed if LIKE condition for DATE column.
mysql> INSERT INTO test001 VALUE ("2019-06-13");
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM test001 WHERE col1 LIKE "2019%";
ERROR 1525 (HY000): Incorrect DATE value: '2019%'
In MySQL (Linux), there is no error.
[environment]
OS is Windows Server 2012 R2
mysql> show global variables like "%sql_mode%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> show global variables like "%character%"\G
*************************** 1. row ***************************
Variable_name: character_set_client
Value: utf8mb4
*************************** 2. row ***************************
Variable_name: character_set_connection
Value: utf8mb4
*************************** 3. row ***************************
Variable_name: character_set_database
Value: utf8mb4
*************************** 4. row ***************************
Variable_name: character_set_filesystem
Value: binary
*************************** 5. row ***************************
Variable_name: character_set_results
Value: utf8mb4
*************************** 6. row ***************************
Variable_name: character_set_server
Value: utf8mb4
*************************** 7. row ***************************
Variable_name: character_set_system
Value: utf8
*************************** 8. row ***************************
Variable_name: character_sets_dir
Value: C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\
8 rows in set, 1 warning (0.00 sec)
How to repeat:
mysql> use test
Database changed
mysql> CREATE TABLE test001 (col1 date);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO test001 VALUE ("2019-06-13");
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM test001 WHERE col1 LIKE "2019%";
ERROR 1525 (HY000): Incorrect DATE value: '2019%'
mysql> SELECT * FROM test001 WHERE col1 LIKE "2019-06%";
ERROR 1525 (HY000): Incorrect DATE value: '2019-06%'
mysql> SELECT * FROM test001 WHERE col1 LIKE "2019-06-1%";
+------------+
| col1 |
+------------+
| 2019-06-13 |
+------------+
1 row in set (0.00 sec)