title: 1292 Truncated incorrect date value: '05/18/2011'
body: <p>I am trying to convert a database with all values stored as VARCHAR into the correct column type eg: INT, DATE, DECIMAL etc... </p>
  
  <p>Problem is, that the columns <code>date_order</code>, <code>date_billed</code> and <code>date_paid</code> are stored in different formats </p>
  
  <p>I still get a <code>1292 Truncated incorrect date value: '05/18/2011'</code> errors and have pretty much no clue what to do since that exact date format is listed as CASE </p>
  
  <p>My code:</p>
  
  <pre><code>SELECT 
        CAST(`ar_no` AS UNSIGNED), 
        CAST(`accession_id` AS UNSIGNED), 
        CAST(`client_id` AS UNSIGNED), 
        CAST(`insurance_id` AS UNSIGNED), 
        CAST(`test_id` AS UNSIGNED), 
  
  CASE 
       WHEN `date_paid` = '0' THEN `date_paid` = '00/00/0000'
       WHEN LENGTH(DATE(STR_TO_DATE(`date_order`, '%m/%d/%y'))) IS NOT NULL THEN STR_TO_DATE(`date_order`, '%m/%d/%y') 
       WHEN LENGTH(DATE(STR_TO_DATE(`date_order`, '%m/%d/%Y'))) IS NOT NULL THEN STR_TO_DATE(`date_order`, '%m/%d/%Y')
  END,
  
  CASE 
       WHEN `date_paid` = '0' THEN `date_paid` = '00/00/0000'
       WHEN LENGTH(DATE(STR_TO_DATE(`date_billed`, '%m/%d/%y'))) IS NOT NULL THEN STR_TO_DATE(`date_billed`, '%m/%d/%y') 
       WHEN LENGTH(DATE(STR_TO_DATE(`date_billed`, '%m/%d/%Y'))) IS NOT NULL THEN STR_TO_DATE(`date_billed`, '%m/%d/%Y')
  END,
  
  CASE 
       WHEN `date_paid` = '0' THEN `date_paid` = '00/00/0000'
       WHEN LENGTH(DATE(STR_TO_DATE(`date_paid`, '%m/%d/%y'))) IS NOT NULL THEN STR_TO_DATE(`date_paid`, '%m/%d/%y') 
       WHEN LENGTH(DATE(STR_TO_DATE(`date_paid`, '%m/%d/%Y'))) IS NOT NULL THEN STR_TO_DATE(`date_paid`, '%m/%d/%Y')
  END,
  
  CAST(`amount_billed` AS DECIMAL(15,2)),
  CAST(`amount_received` AS DECIMAL(15,2)),
  CAST(`amount_adjusted` AS DECIMAL(15,2))
  
  FROM `acs`.`billing_unformatted`;
  </code></pre>