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?

解决方案

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.