DATEADD in MariaDB

I’m using DATEADD statement in SQL Server and I need migration to MariaDB

  SUM (
    CASE
    WHEN CONVERT(varchar, Production.MadeDate , 112) BETWEEN DATE_ADD(DAY, -2, '2018-06-05') AND DATE_ADD(DAY, -2, '2018-06-05') THEN
      Production.Qty
    ELSE
      0
    END
  ) AS 'N-2'

And i got error like this

[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘varchar, Production.MadeDate , 112) BETWEEN DATE_ADD(DAY, -2, ‘2018-06-05’) AND ‘ at line 3

I’m got references from MariaDB DATE_ADD and MariaDB ADDDATE but it’s still doesn’t working

My Version MariaDB 10.1.32-MariaDB

EDIT :

[SOLVED]

Changing the SQL Statment from CONVERT to CAST

  SUM (
    CASE
    WHEN CONVERT(varchar, Production.MadeDate , 112) BETWEEN DATE_ADD(DAY, -2, '2018-06-05') AND DATE_ADD(DAY, -2, '2018-06-05') THEN
      Production.Qty
    ELSE
      0
    END
  ) AS 'N-2'

TO

SUM (
    CASE WHEN CAST(Production.MadeDate AS DATE) BETWEEN DATE_ADD('2018-06-05', INTERVAL -2 DAY) AND DATE_ADD('2018-06-05', INTERVAL -2 DAY) THEN
        Production.Qty
    ELSE
        0
    END
) AS 'N-2'

It’s working for me on

10.1.32-MariaDB

Answer

You can’t use the CONVERT like this on MariaDB / MySQL:

CONVERT(varchar, Production.MadeDate, 112)

The order of the parameters isn’t valid on MariaDB / MySQL. The order of the parameters looks like TSQL / SQL Server syntax.

So you can replace the current CONVERT with one of the following:

CONVERT(Production.MadeDate, DATE)  -- using CONVERT (ODBC syntax)
CAST(Production.MadeDate AS DATE)   -- using CAST (SQL92 syntax)

You can use the following SUM using CAST and DATE_ADD:

SUM (
    CASE WHEN CAST(Production.MadeDate AS DATE) BETWEEN DATE_ADD('2018-06-05', INTERVAL -2 DAY) AND DATE_ADD('2018-06-05', INTERVAL -2 DAY) THEN
        Production.Qty
    ELSE
        0
    END
) AS 'N-2'

Note: Check the condition on CASE WHEN also. You check between the same days.