Bug #57869 INSERT ... SELECT with data manipulation
Submitted: 31 Oct 2010 10:21 Modified: 11 Feb 2018 13:06
Reporter: Adrian Benko Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.1.51-community OS:Windows (7)
Assigned to: CPU Architecture:Any

[31 Oct 2010 10:21] Adrian Benko 
Description:
Script produce an error (Truncate value ....)

How to repeat:
CREATE TABLE test1 (
  String_Column varchar(255)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO test1 (String_Column) VALUES ('12/23/09 0:00:00');

CREATE TABLE test2 (
  Date_Column DATE
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO test2 (Date_Column)
  SELECT STR_TO_DATE(String_Column, '%c/%e/%Y') 
  FROM ods.test1;
[31 Oct 2010 11:24] Valeriy Kravchuk 
Looks like you had forgotten about hours, minutes and seconds in the string you process, hence that warning (or error). Look:

mysql> INSERT INTO test2 (Date_Column)
    ->   SELECT STR_TO_DATE(String_Column, '%c/%e/%Y') 
    ->   FROM test1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect date value: '12/23/09 0:00:00'
1 row in set (0.00 sec)

mysql> INSERT INTO test2 (Date_Column)   SELECT STR_TO_DATE(String_Column, '%c/%e/%Y %k:%i:%s')    FROM test1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from test2;
+-------------+
| Date_Column |
+-------------+
| 2009-12-23  |
| 2009-12-23  |
+-------------+
2 rows in set (0.00 sec)
[31 Oct 2010 14:59] Adrian Benko 
But "SELECT STR_TO_DATE(String_Column, '%c/%e/%Y') FROM test1;" will return "2009-12-23" without warning (or error). It should return "null", because the string is in different format. How can I check if a string is in correct format?
[31 Oct 2010 15:01] Adrian Benko 
Question: Why you have got a warning and I have got an error?
[31 Oct 2010 15:07] Peter Laursen 
@Adrian .. probably because you and Valeriy use different sql_modes.

Try 

SET SQL_MODE = '';
SELECT @@session.SQL_MODE;
SELECT @@global.SQL_MODE;

(this - SQL_MODE = '' - is MySQL default, but the Windows installer/config wizard sets up a 'strict' (global) SQL_mode) unless you specify otherwise.)

Peter
(not a MySQL person)
[1 Nov 2010 11:46] MySQL Verification Team 
Please try what Peter commented.
[2 Dec 2010 0:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[11 Feb 2018 13:06] Roy Lyseng 
Posted by developer:
 
Closing since bug was suspended for 7 years.