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%'

Gordon Linoff · Accepted Answer · 2018-02-13T13:24:00.680

2

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.

edited Feb 13 '18 at 13:24

answered Feb 13 '18 at 12:01

Gordon Linoff

1,242,037
58
646
786

Yes this works but it is much longer than LIKE. Thanks. Please correct the question marks in the code. – Čamo Feb 13 '18 at 12:31
using between is a shorter approach if you feel this is to long. See https://stackoverflow.com/q/3822648/3664960 and https://www.techonthenet.com/mysql/between.php – davejal Feb 19 '18 at 12:11
@Čamo . . . There are no question marks in this answer. – Gordon Linoff Feb 20 '18 at 02:19
Yes it should be - single quotes. Sorry my English is little confused. – Čamo Feb 20 '18 at 11:11

score 0 · Answer 2 · answered Feb 19 '18 at 12:14

0

As Gordon already explained, don't use like for date fields.

Another option would be the use of between

WHERE tasks.date_to_resolve between '2018-01-01' AND '2018-02-01' 
  AND surveys.ended between '2018-02-01' AND '2018-03-01'

See between question on SO and techonthenet.com/mysql/between.php

answered Feb 19 '18 at 12:14

davejal

6,009
10
39
82

It solves the problem but I need to use it with ORM which does not know between clause. Thanks. – Čamo Feb 20 '18 at 11:13

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

2 Answers2