title: MySQL: ON DUPLICATE KEY UPDATE gives Truncated incorrect date value error
body: <p>My query should be simple...</p>
  
  <pre><code>INSERT INTO email_final_list
  (E_mail, Title, First_Name, Last_Name, ZIP_Code, AddMethod, Update_Date, Status)
  SELECT
      DISTINCT E_mail,
      Title,
      First_Name,
      Last_Name,
      ZIP_Code,
      CASE WHEN AddMethod IS NULL THEN 'Opera' ELSE 1 END as AddMethod,
      Update_Date,
      Status
  FROM guest_profiles
  WHERE E_mail NOT LIKE '' AND ifnull(Update_Date, '') &lt;&gt; '' AND STR_TO_DATE(Update_Date,'%d/%m/%Y') &gt; CURDATE() - INTERVAL 5 DAY
  ON DUPLICATE KEY UPDATE
      email_final_list.E_mail = guest_profiles.E_mail,
      email_final_list.Title = guest_profiles.Title,
      email_final_list.First_Name = guest_profiles.First_Name,
      email_final_list.Last_Name = guest_profiles.Last_Name,
      email_final_list.ZIP_Code = guest_profiles.ZIP_Code,
      email_final_list.AddMethod = guest_profiles.AddMethod,
      email_final_list.Status = guest_profiles.Status,
      email_final_list.Update_Date = guest_profiles.Update_Date
  </code></pre>
  
  <p>The 'Update Date' fields on BOTH tables are VarChar (no idea why, but this cannot be changed). But when I run this, it returns</p>
  
  <pre><code>[Err] 1292 - Truncated incorrect date value: '11/4/2013 00:00:00'
  </code></pre>
  
  <p>The really odd thing is that if I run the SELECT part on it's own, I don't get any results.</p>
  
  <p>The database was inherited so can't be changed - the schema &amp; names are all over the place, but unfortunately, I have to work with it.</p>
  
  <p>Does anyone know what I've done wrong here?</p>
  
  <p>Thanks</p>
  
  <p>Edit: the EXACT code is</p>
  
  <pre><code>INSERT INTO `email-final-list`
  (`E-mail`, Title, `First Name`, `Last Name`, `ZIP Code`, AddMethod, `Update Date`, `Status`)
  SELECT
      DISTINCT `E-mail`,
      Title,
      `First Name`,
      `Last Name`,
      `ZIP Code`,
      CASE WHEN AddMethod IS NULL THEN 'Opera' ELSE 1 END as AddMethod,
      `Update Date`,
      `Status`
  FROM `guest-profiles`
  WHERE `E-mail` NOT LIKE '' AND ifnull(`Update Date`, '') &lt;&gt; '' AND STR_TO_DATE(`Update Date`,'%d/%m/%Y') &gt; CURDATE() - INTERVAL 5 DAY
  ON DUPLICATE KEY UPDATE
      `email-final-list`.`E-mail` = `guest-profiles`.`E-mail`,
      `email-final-list`.Title = `guest-profiles`.Title,
      `email-final-list`.`First Name` = `guest-profiles`.`First Name`,
      `email-final-list`.`Last Name` = `guest-profiles`.`Last Name`,
      `email-final-list`.`ZIP Code` = `guest-profiles`.`ZIP Code`,
      `email-final-list`.AddMethod = `guest-profiles`.AddMethod,
      `email-final-list`.`Status` = `guest-profiles`.`Status`,
      `email-final-list`.`Update Date` = `guest-profiles`.`Update Date`
  </code></pre>
  
  <p>But I cannot change the database fields AT ALL.</p>