Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Duplicate
-
10.2
-
None
-
Testing on some MariaDB Version and also MySQL
Description
If i have a SELECT query that returns 1 from 2 ROWS everything is OK. But when i add it afer a INSERT it reports a ERROR on the second ROW
Sample CODE
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;
INSERT INTO `table1` (`id`, `dt_int`)
VALUES
(1, 20200202),
(2, 20202020);
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;
– OK it returns only 1 correct ROW
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;
– reports ERROR – Incorrect datetime value: '20202020' for function str_to_date
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
;
How can i fix this in the query
Attachments
Issue Links
- duplicates
-
MDEV-17800 STR_TO_DATE errors when used during an INSERT
- Closed