CODEWITHSUNDEEP
sqlmariadb
Muirik
Muirik

Reputation: 6289

Datetime Issue in MariaDB SQL Query

I am converting some SQL queries that we were running via SQL Anywhere to work with a new MariaDB environment. One of the queries that's giving me trouble is this one:

UPDATE
  rfo.client_notes_details
SET
  client_notes_details.equip_status = 59
WHERE
  (
    (
      (client_notes_details.equip_status) BETWEEN 4
      AND 58
    )
    AND (
      (client_notes_details.date_of_visit) < NOW() - 30
    )
  );

When I run the above query in a SQL checker it works. However, when I test this against our MariaDB database with SQL Workbench I get the following datetime issue:

1292 Incorrect datetime value: '20191028072188' Rows matched: 0 Changed: 0 Warnings: 1

How can I update this query to use a datetime format that's MariaDB-friendly?

Upvotes: 0

Views: 393

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Use interval:

client_notes_details.date_of_visit < NOW() - INTERVAL 30 DAY

MySQL and MariaDB have a tendency to convert date/time values to strings or numbers depending on the context. In this case, it sees that a number is being subtracted, so it converts the value to a number -- and the result is not a valid date.

Upvotes: 2

Related Questions