I'm using MySQL 5.6 and I have a program that runs the following SQL statement against my database:
UPDATE `m_table` SET `s_time` = '2013-08-25T17:00:00+00:00' WHERE id = '123'
Unforutnately, I get the following error:
Incorrect datetime value: '2013-08-25T17:00:00+00:00' for column 's_time' at row 1
The datatype for s_time is DateTime.
I have already attempted to set the allow_invalid_dates property using the workbench.
Can anyone understand and please explain this error to me? I know that if I manually change the statement to UPDATE m_table
SET s_time
= '2013-08-25 17:00:00' WHERE id = '123', the statement works.
Unfortunately, I cannot modify the program that supplies the SQL statement (which I'm told is valid by the creator of the program) and I also cannot understand what the +00:00 symbolises.
Thanks
Best Answer
This is a valid iso-8601 datetime value, but it is not a valid MySQL datetime literal. On that point, the developer is incorrect.
The documentation explains what
ALLOW_INVALID_DATES
does:In other words,
2013-02-31
would be a permissible date ifallow_invalid_dates
is set. This option does not do anything when the date or datetime isn't even in a valid format for MySQL.The
+00:00
is the timezone offset from UTC. In this case, the time expressed is in UTC, so the offset is zero hours, zero minutes.Your workaround would be to remove the
STRICT_TRANS_TABLES
from thesql_mode
that is a default in the config file created during the MySQL 5.6 installation process... you need to carefully consider the implications of changing this, but it does allow the data to go in.