SugarCRM: Convert Text field to Date

-- add tmp field for safety
alter table contacts_cstm add temp_rfc_rcd_c varchar(255);
-- update tmp field data. Should be around
update contacts_cstm set temp_rfc_rcd_c = rfc_rcd_c;
-- count... ~1192
select count(*) from contacts_cstm where temp_rfc_rcd_c is not null and temp_rfc_rcd_c <> '';
-- make the change
alter table contacts_cstm modify rfc_rcd_c date;
-- cleanup... because 0000-00-00  is useless
update contacts_cstm set rfc_rcd_c = null where rfc_rcd_c = '0000-00-00';
-- compare data integrity post- datatype change. ~1192 again?
select count(*) from contacts_cstm where rfc_rcd_c is not null and rfc_rcd_c <> '';


-- escape plan, if data integrity didn't hold well (3%)
alter table contacts_cstm modify rfc_rcd_c varchar(255);
update contacts_cstm set rfc_rcd_c = temp_rfc_rcd_c;

-- If < 3% of data loss, or if it's perfect
update fields_meta_data set
type='date',len=null,date_modified=NOW()
 where id = 'Contactsrfc_rcd_c';

-- moving forward with some data loss, 0-3%, please send me a backup file
-- with the temp_rfc_rcd_c column still in the table. I'll manually fix.

-- if counts match exactly, go ahead and drop temp_rfc_rcd_c
alter table contacts_cstm drop column temp_rfc_rcd_c;