Import not liking .CSV file dates in format mm/dd/yyyy

I'm trying to import data (many columns) and the date fields in this CSV file are formatted like 12/31/2010. I get the following in the Warnings afterwards:

Data truncated for column 'frst_dt' at row 1

...and for the next 64 rows (warning limit set to 64).

Visually looking at data after import: dates are all zeros (0000-00-00).

How do I get HeidiSQL to recognize the MM/DD/YYYY format in my import files?

Thanks,
--Mike

I would use VARCHAR columns for these columns, then import these as simple string values. Afterwards you can fix your dates via UPDATE/CONCAT.

ALTER TABLE `mytable` CHANGE COLUMN `datecol` `datecol` VARCHAR(20) NULL DEFAULT NULL;
<do the import>
UPDATE mytable SET datecol=CONCAT_WS('-', SUBSTR(datecol, 7, 4), SUBSTR(datecol, 1, 2), SUBSTR(datecol, 4, 2));
ALTER TABLE `mytable` CHANGE COLUMN `datecol` `datecol` DATE NULL DEFAULT NULL;

neat workaround. I'll try it. Thanks!

Anse -- this worked. Thanks again. I would suggest, though, a future enhancement request/feature that accounts for this date format, thus getting it inputted right the first time without need for re-work. ???

I think this is too special for being implemented in HeidiSQL. Please keep in mind, you have this format in your file, other users may have totally different date formats, and tomorrow you say you have a file with another date format in it.

Really? Ok. I just figured this was one of the "usual American" kind of things that might not be so outside the USA's borders due to customs/defaults, but within the USA, it might be prevalent. And if that's the case, then a lot of users in the USA would benefit from it. Then again, perhaps this is a rare situation (but I wouldn't think so).

Oh well. Just a thought. Still...thanks so much again for your workaround!

I think the above ALTER/UPDATE fix is much more powerful here.

A better solution is to reformat the date in excel in half a second in to a yyyy-mm-dd formate and import right into the date field.

Bringing Excel into the equation means another tool required. If you've got the latest Excel, fine, but if you've got an earlier version where there's a row limit of well less than a million, this option isn't valid. (And for my data, there's more than a million rows.) I'd like to not introduce more links in the chain if at all possible.