Mysql STR_TO_DATE incorrect datetime value

Take a close look at the error message:

Incorrect datetime value: ''12-16-2010 01:48:28''
                          ^^  2 single quotes  ^^

Compare this to the normal error message:

mysql> SELECT STR_TO_DATE('foo', '%c-%e-%Y %T');
+-----------------------------------+
| STR_TO_DATE('foo', '%c-%e-%Y %T') |
+-----------------------------------+
| NULL                              |
+-----------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: 'foo' for function str_to_date |
+---------+------+----------------------------------------------------------+
1 row in set (0.00 sec)                      ^   ^ just 1 single quote 

Normally, the error message has a single set of single quotes. Yours has a double set, suggesting that you actually have a set of single quotes stored in your column data.

If this is the case, you can work around this by removing them where they exist:

SET datetimefile = (SELECT STR_TO_DATE(REPLACE(datestring,"'",''), '%c-%e-%Y %T')) 

Using REPLACE() like this still would work even if not all of the rows contain the spurious quotes, since replace passes through the input value unchanged if the 'from_str' (2nd arg) doesn't occur.

From PHP SQL Request Correct:

LOAD DATA INFILE '.$filename."' INTO TABLE tablename (@var_DTime, `Product`, `Source`, `Cost`) SET `DTime` = str_to_date(@var_DTime,'%Y-%m-%dT%H:%i:%s')

Do not use: " " - 2010-12-31 01:48:28; - Don't work

Use "T" - 2010-12-31T01:48:28; - Work