Mysql STR_TO_DATE incorrect datetime value
datetime, mysql
Solution
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.
Problem
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?