title: Cannot change field type from text to date after field is used in concat - mysql
body: <p>In a table, I created a new field/column then set the column to populate as a concat of two other fields.  The two fields used in the concat are text fields.  I executed the concat just fine.  However, I need to convert one of the two text fields used in the concat to a date format (the field values are already populated as dates but in text format).  When I change the format of the field from text to date, it results in the column populating with all zeros in date format (00-00-0000).  Initially, I figured this was somehow being caused by the concat the field used to populate (even though theoretically it shouldn't), so I deleted the field/column where the concat was populating, went back and changed the format of the field from text to date, and this resulted in the field values all changing to zeros once again (00-00-0000).</p>
  
  <p>In troubleshooting this issue, I found that it is possible to change the desired field's format to types other than date (for example from varchar to bigint or text).  If I change the format before executing the concat, the format changes from text to date perfectly fine; so it seems as though once the text field is used in the concat, something happens to the integrity of the field values so that if the text field is converted to a date field after the text field is used in a concat, the values will change to 00-00-0000.  In addition, I created a new empty column, copied the text column that I want to convert to a date, and pasted it in the empty column; the data pastes exactly how it is, but then when I change the format the field values change to 00-00-0000 again.</p>
  
  <p>In a total of five DB tables comprised of over 80M total rows, I created an additional column and ran concats that use two fields in each table to populate the new column.  The fields used in the concat are both text fields and the field populated with the concat is a varchar field.</p>
  
  <p>I'm in the very early stages of building this database, so if the only logical solution to this problem is to delete the database and recreate it, I will do so; however, finding a solution that doesn't involve deleting and loading the data in again, without compromising the DB's integrity as a whole, would be ideal.</p>
  
  <p>I'm running an RDS Aurora DB w/ an InnoDB storage engine and using Navicat mysql as a DBMS.</p>
  
  <p>Thank you in advance for any assistance you might be able to provide regarding this issue.</p>