Mysql - Incorrect datetime value in where clause with datetime LIKE '2018-01%'

Question

Can somebody tell me please why this command throws me an error if there is LIKE clasue on datetime? Here is the code:

UPDATE surveys
LEFT JOIN tasks ON surveys.task_id = tasks.id
SET surveys.ended = tasks.date_to_resolve
WHERE tasks.date_to_resolve LIKE '2018-01%' AND surveys.ended LIKE '2018-02%'

It throws the error "Incorrect datetime value: '2018-01%' for column 'date_to_resolve' at row 1" The strangest thing is the SELECT statement works well with the same condition

SELECT * FROM surveys
LEFT JOIN tasks ON surveys.task_id = tasks.id
WHERE tasks.date_to_resolve LIKE '2018-01%' AND surveys.ended LIKE '2018-02%'

Answer 1

Do not use like for dates. MySQL has very good support for actual date functions.

So:

WHERE tasks.date_to_resolve >= '2018-01-01' AND tasks.date_to_resolve < '2018-02-01' AND
      surveys.ended >= '2018-02-01' AND surveys.ended < '2018-03-01'

This prevents the implicit conversion between dates and times and allowes the engine to make use of optimizers (if the appropriate ones are available).

EDIT:

The proper update query is:

UPDATE surveys s JOIN
       tasks t
       ON s.task_id = t.id
    SET s.ended = t.date_to_resolve
    WHERE t.date_to_resolve >= '2018-01-01' AND t.date_to_resolve < '2018-02-01' AND
          s.ended >= '2018-02-01' AND surveys.ended < '2018-03-01';

The LEFT JOIN is being undone by the WHERE, so you might as well express the join correctly.