title: #1411 - Incorrect datetime value for function str_to_date on INSERT INTO...SELECT
body: <p>These queries require the string to date conversion because Timestamp is stored as a string, and the logging application is unchangeable.</p>
  
  <p>I have a select query that works perfectly -></p>
  
  <pre><code>(SELECT main.user_id, main.Timestamp
  FROM `user_table` main
  WHERE STR_To_DATE( main.Timestamp , '%a %b %d %H:%i:%s CST %Y' ) &lt;
    (SELECT  MAX(STR_To_DATE( sub.Timestamp , '%a %b %d %H:%i:%s CST %Y' )) 
     FROM `user_table` sub
     WHERE sub.user_id = main.user_id ))
  </code></pre>
  
  <p>This will select ALL BUT most recently added user id and timestamp from my table.</p>
  
  <p>However, when I try to insert this into another table...like so -></p>
  
  <pre><code>INSERT INTO user_table_temp (`user_id`, `Timestamp`)
  (SELECT main.user_id, main.Timestamp
  FROM `user_table` main
  WHERE STR_To_DATE( main.Timestamp , '%a %b %d %H:%i:%s CST %Y' ) &lt;
    (SELECT  MAX(STR_To_DATE( sub.Timestamp , '%a %b %d %H:%i:%s CST %Y' )) 
     FROM `user_table` sub
     WHERE sub.user_id = main.user_id ))
  </code></pre>
  
  <p>I get the following error -></p>
  
  <pre><code>#1411 - Incorrect datetime value: 'Mon Mar 14 16:10:10 CDT 2011' for function str_to_date
  </code></pre>
  
  <p>The reason for this is because my Timestamps are stored in two formats. One will store as 'CST' during non-daylight-savings time, the other will store as 'CDT' during daylight-savings time. When the INSERT INTO SELECT gets to the first row that has the opposite of what I'm querying against, it will fail with the above message.</p>
  
  <p>I have also tried to COALESCE them, which also works when just running the select -></p>
  
  <pre><code>INSERT INTO user_table_temp  (`user_id `, `Timestamp`)
  (SELECT main.user_id , main.Timestamp
  FROM `user_table` main
  WHERE COALESCE(STR_To_DATE( main.Timestamp , '%a %b %d %H:%i:%s CDT %Y' ), STR_To_DATE( main.Timestamp , '%a %b %d %H:%i:%s CST %Y' )) &lt;
    (SELECT  MAX(COALESCE(STR_To_DATE( sub.Timestamp , '%a %b %d %H:%i:%s CDT %Y' ), STR_To_DATE( sub.Timestamp , '%a %b %d %H:%i:%s CST %Y' ))) 
     FROM `user_table` sub
     WHERE sub.user_id = main.user_id ))
  </code></pre>
  
  <p>Why would this fail on an INSERT, but the SELECT would work?</p>