Rebecca Meritz
Rebecca Meritz

Reputation: 722

Query that works with MariaDB but fails with a syntax error on MySQL

This query works on MariaDB, but not MySQL. I get a error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'integer) HOUR

This is a really dense query. So, even though I've looked through the manual I've had trouble identify which function is actually causing the issue.

SELECT  1 AS one
FROM sched_walks INNER JOIN walks ON walks.id = sched_walks.walk_id
WHERE sched_walks.d_acc = 1
  AND sched_walks.docent_id = 6
  AND (sched_walks.id != 714)
  AND (
    scheduled_for_utc <= '2018-03-17 14:34:31.000000'
      AND
    DATE_ADD(
      scheduled_for_utc,
      INTERVAL CONVERT(REPLACE(COALESCE(sched_duration, walks.duration), ' hours', ''), integer) HOUR
    ) >= '2018-03-17 12:34:31.000000'
  )
LIMIT 1

Upvotes: 0

Views: 252

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Use cast() instead:

  INTERVAL CAST(REPLACE(COALESCE(sched_duration, walks.duration), ' hours', '') as signed) HOUR

Actually, you can use convert() . . . the key is using signed or unsigned.

Or just use silent conversion:

  INTERVAL COALESCE(sched_duration, walks.duration) + 0 HOUR

Upvotes: 2

Related Questions