MariaDB中的DATEADD(DATEADD in MariaDB)

MariaDB中的DATEADD(DATEADD in MariaDB)

我在SQL Server中使用DATEADD语句，我需要迁移到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'

我得到这样的错误

[42000] [1064]你的SQL语法有错误; 查看与您的MariaDB服务器版本相对应的手册，查看在'varchar，Production.MadeDate，112'附近使用的正确语法）BETWEEN DATE_ADD（DAY，-2，'2018-06-05'）AND'at line 3

我从MariaDB DATE_ADD和MariaDB ADDDATE获得了引用，但它仍然不起作用

我的版本MariaDB 10.1.32-MariaDB

编辑：

[解决了]

将SQL状态从CONVERT更改为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'

至

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'

它为我工作

10.1.32-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

最满意答案

你不能像这样使用CONVERT ：

CONVERT(varchar, Production.MadeDate, 112)

这是TSQL / MSSQL语法 ，不能在MariaDB或MySQL上像这样使用。

所以你可以试着用下面的一种替换当前的CONVERT ：

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

您可以使用CAST和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'

注意：请检查CASE WHEN的情况。 你在同一天之间检查。

You can't use the CONVERT like this:

CONVERT(varchar, Production.MadeDate, 112)

This is TSQL/MSSQL syntax and can't be used like this on MariaDB or MySQL.

So you can try to 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 try the following query 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.