需要将sql server查询转换为mysql查询

I've need to convert following sql server query into mysql

我需要将以下sql server查询转换为mysql

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, p_date)) [date],
       SUM(p_amount) [sum]
FROM tbl_Payments
WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, p_date)) BETWEEN '20130701' AND '20130731'
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, p_date))

column names: p_date,p_amount

列名:p_date,p_amount

1 个解决方案

#1

Your query looks correct except few things like

您的查询看起来正确,除了少数事情

Replace [] with backticks "`" for escaping

用反引号替换[]以逃避
DATE and TIME function syntax are different in MySQL than SQL Server. For more information on MySQL DATE and TIME functions see Date and Time Functions

DATE和TIME函数语法在MySQL中与SQL Server不同。有关MySQL DATE和TIME函数的更多信息,请参阅日期和时间函数
Try using a minimum date like 1-1-1 instead of 0

尝试使用最小日期,如1-1-1而不是0

select DATEDIFF('1-1-1', now())

选择DATEDIFF('1-1-1',now())

select DATE_ADD('1-1-1', interval DATEDIFF(now(), '1-1-1') day);

选择DATE_ADD('1-1-1',区间DATEDIFF(now(),'1-1-1')日);

With all this the below query should work fine in MySQL

有了这一切,下面的查询应该在MySQL中正常工作

SELECT DATE_ADD('1-1-1', interval DATEDIFF(p_date, '1-1-1') day) `date`,
         SUM(p_amount) `sum`
         FROM tbl_Payments
         WHERE DATE_ADD('1-1-1', interval DATEDIFF(p_date, '1-1-1') day)
         BETWEEN '20130701' AND '20130731'
        GROUP BY DATE_ADD('1-1-1', interval DATEDIFF(p_date, '1-1-1') day)

#1