title: How to do a SQL INSERT of a timestamp in new column of table with cast to data type DATETIME
body: <p>I have a table with a column 'TIMESTAMP' of data type VARCHAR:</p>
  <pre><code>CREATE TABLE Table1
      (`ID` int, `TIMESTAMP` varchar(24), `TOPIC` varchar(30), `PAYLOAD` numeric, `UNIT` varchar(5), `SENSOR` varchar(25), `DerivedValue` int, `DerivedValueDesc` varchar(17))
  ;
      
  INSERT INTO Table1
      (`ID`, `TIMESTAMP`, `TOPIC`, `PAYLOAD`, `UNIT`, `SENSOR`, `DerivedValue`, `DerivedValueDesc`)
  VALUES
      (17463, '2019-11-28T17:58:12.776Z', 'Wago-855-9150', 475.29334, 'W', '&quot;Wago 855-9150/2000-0701&quot;', 1, '&quot;Produktion ja&quot;'),
      (17464, '2019-11-28T17:58:13.675Z', 'ifm-O5D150', 19.0625, 'cm', '&quot;ifm O5D150&quot;', 1, '&quot;Tür offen&quot;'),
      (17465, '2019-11-28T17:58:13.675Z', 'Keyence-FD-Q20C', 0, 'L/min', '&quot;Keyence FD-Q20C&quot;', 0, '&quot;Durchfluss nein&quot;'),
      (17466, '2019-11-28T17:58:14.676Z', 'ifm-O5D150', 23.0625, 'cm', '&quot;ifm O5D150&quot;', 0, '&quot;Tür zu&quot;'),
      (17467, '2019-11-28T17:58:14.676Z', 'Keyence-FD-Q20C', 0, 'L/min', '&quot;Keyence FD-Q20C&quot;', 0, '&quot;Durchfluss nein&quot;'),
      (17468, '2019-11-28T17:58:14.776Z', 'Wago-855-9150', 569.14666, 'W', '&quot;Wago 855-9150/2000-0701&quot;', 1, '&quot;Produktion ja&quot;')
  ;
  </code></pre>
  <p>It turns out now that querying the table is very time consuming:</p>
  <pre><code>SELECT PAYLOAD FROM Table1 WHERE TOPIC LIKE '%temperature%' AND CAST(TIMESTAMP AS datetime) between '2019-09-29 00:00:00' and '2019-09-29 00:59:59';
  </code></pre>
  <p>So the idea is to create an additional column and store right there the timestamp in data type DATETIME:</p>
  <pre><code>ALTER TABLE Table1 ADD `DATETIME` DATETIME AFTER `TIMESTAMP`; 
  </code></pre>
  <p>Then I want to fill the new column with the converted data from the column TIMESTAMP:</p>
  <pre><code>INSERT INTO Table1(DATETIME) SELECT CAST(`TIMESTAMP` AS DATETIME) FROM Table1;
  </code></pre>
  <p>Then i get the error message:</p>
  <blockquote>
  <p>Error: ER_TRUNCATED_WRONG_VALUE: Truncated incorrect datetime value:
  '2019-11-28T17:58:12.776Z'</p>
  </blockquote>
  <p>I get this error while prototyping the query in <a href="https://www.db-fiddle.com/" rel="nofollow noreferrer">https://www.db-fiddle.com/</a> and <a href="http://sqlfiddle.com/" rel="nofollow noreferrer">http://sqlfiddle.com/</a>. I am using an MySQL database v5.7.</p>
  <p>Interestingly this query works:</p>
  <pre><code>SELECT `TIMESTAMP`, CAST(`TIMESTAMP` AS DATETIME) AS `MYDATETIME` FROM Table1;
  </code></pre>
  <p>But i need the data in the new column not in a query.</p>
  <p>What is my mistake? I think it is better not to take string operations like STR_TO_DATE because of uncertain consquences to the rounding of the seconds.</p>