MySQL INSERT INTO SELECT not matching records are triggered by function str_to_date

Question

I'm having a weird behavior of MySQL INSERT SELECT where I need to convert the dt_int from TABLE2 to the date time dt in TABLE1.

The table structure is

TABLE1
PK INT(11) -- auto increment
dt datetime

TABLE2
PK INT(11)  -- auto increment
dt_int INT(11)

I have as insert select query like this

INSERT INTO TABLE1(dt)
(
  SELECT str_to_date(dt_int, '%Y%m%d')
  FROM TABLE2 
  WHERE str_to_date(dt_int, '%Y%m%d') IS NOT NULL
)

It works fine if all the dates in the table are valid. However if the table consists of data similar like this

TABLE2
PK   |   dt_int
1        20201209
2        20202020

it would hit Error Code 1411: Incorrect datetime value '20202020' for function str_to_date.

The internal select statements returns only the valid dates, but the insert statements still try to converts the date for those that are filtered. Why is this happening? Is there anything I can do?

[Edited] The MySQL version is 5.7 and engine is InnoDB. Currently hosted in Windows environment.

I can reproduce your issue but I have no idea why there is this problem (in 5.7 and 8.0) — P.Salmon, Mar 03 '20 at 07:33
The solution given by Akina solves the problem, but have no idea why MySQL have this issue with str_to_date — SomeoneWhoCodes, Mar 03 '20 at 07:43
Oo I see... there's no month '20'.. I think it should be '20200220' — FanoFN, Mar 03 '20 at 07:44
*why MySQL have this issue with str_to_date* You cannot understand why INCORRECT date literal cannot be CORRECTLY converted to date datatype? — Akina, Mar 03 '20 at 07:45
@akina that's not really the issue - if you run the select by itself then only 1 record is returned and no error is reported , If you run the select inside the insert then an error occurs. — P.Salmon, Mar 03 '20 at 07:48

score 0 · Accepted Answer · answered Mar 03 '20 at 07:24

0

INSERT INTO table1
SELECT PK, CONCAT(dt_int DIV 10000, '-1-1') 
           + INTERVAL dt_int MOD 10000 DIV 100 - 1 MONTH 
           + INTERVAL dt_int MOD 100 - 1 DAY
FROM table2
WHERE 0 + DATE_FORMAT(CONCAT(dt_int DIV 10000, '-1-1') 
                      + INTERVAL dt_int MOD 10000 DIV 100 - 1 MONTH  
                      + INTERVAL dt_int MOD 100 - 1 DAY, '%Y%m%d') = dt_int;

fiddle

answered Mar 03 '20 at 07:24

Akina

39,301
5
14
25

Why is the 0 + required in the WHERE? – SomeoneWhoCodes Mar 03 '20 at 07:48
1

@SomeoneWhoCodes This is explicit convertation to numeric datatype. It seems it may be skipped... but I don't like implicit datatype convertions and surprises they may bring me up. – Akina Mar 03 '20 at 07:50

Bernd Buffen · Answer 2 · 2020-03-03T15:25:55.497

You can set/unset the SLQ_MODE like this. Then it working

SELECT REPLACE(@@SQL_MODE, ',', '\n');

SET @@SQL_MODE = REPLACE(@@SQL_MODE, ',STRICT_TRANS_TABLES', '');

INSERT INTO table2(dt) SELECT * FROM (
SELECT DATE(str_to_date( dt_int , '%Y%m%d')) 
FROM table1
WHERE date(str_to_date( dt_int, '%Y%m%d')) is not null
) as x;

see also SQL_MODE

Sample

Create Tables

mysql> CREATE TABLE `table1` (
    ->   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   `dt_int` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO `table1` (`id`, `dt_int`)
    -> VALUES
    ->     (1, 20200202),
    ->     (2, 20202020);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE `table2` (
    ->   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   `dt` date DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

Show Data

mysql> SELECT DATE(str_to_date( dt_int , '%Y%m%d'))
    -> FROM table1
    -> WHERE date(str_to_date( dt_int, '%Y%m%d')) is not null;
+---------------------------------------+
| DATE(str_to_date( dt_int , '%Y%m%d')) |
+---------------------------------------+
| 2020-02-02                            |
+---------------------------------------+
1 row in set, 2 warnings (0.00 sec)

Run Query with Error

mysql> INSERT INTO table2(dt)
    -> SELECT DATE(str_to_date( dt_int , '%Y%m%d'))
    -> FROM table1
    -> WHERE date(str_to_date( dt_int, '%Y%m%d')) is not null;
ERROR 1411 (HY000): Incorrect datetime value: '20202020' for function str_to_date
mysql>

Get SQL_MODE and remove it

mysql>
mysql> SELECT REPLACE(@@SQL_MODE, ',', '\n');
+-------------------------------------------------------------------------------------------------------------------------------------------+
| REPLACE(@@SQL_MODE, ',', '\n')                                                                                                            |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY
STRICT_TRANS_TABLES
NO_ZERO_IN_DATE
NO_ZERO_DATE
ERROR_FOR_DIVISION_BY_ZERO
NO_AUTO_CREATE_USER
NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET @@SQL_MODE = REPLACE(@@SQL_MODE, ',STRICT_TRANS_TABLES', '');
Query OK, 0 rows affected, 1 warning (0.00 sec)

Run Query without Error

mysql> INSERT INTO table2(dt) SELECT * FROM (
    -> SELECT DATE(str_to_date( dt_int , '%Y%m%d'))
    -> FROM table1
    -> WHERE date(str_to_date( dt_int, '%Y%m%d')) is not null
    -> ) as x;
Query OK, 1 row affected, 2 warnings (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 2

mysql>

@SomeoneWhoCodes - i have a Answer for you – Bernd Buffen Mar 03 '20 at 15:31 — Bernd Buffen, Mar 03 '20 at 15:31

MySQL INSERT INTO SELECT not matching records are triggered by function str_to_date

2 Answers2