Mysql STR_TO_DATE incorrect datetime value

Question

I've loaded some date from file to table and now i want to convert the string with date to a datetime format. The string i 'datestring' column looks like this '12-16-2010 01:48:28', and if i run this query:

select STR_TO_DATE('12-16-2010 01:48:28', '%c-%e-%Y %T')

It returns proper datetime: 2010-12-16 01:48:28

But when i try to run this:

update database.`temptable` 
SET datetimefile = (SELECT STR_TO_DATE(datestring, '%c-%e-%Y %T'))

I get those kind of errors:

Incorrect datetime value: ''12-16-2010 01:48:28'' for function str_to_date

Any ideas?

Answer 1

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.