从MS-SQL Server查询转换为MySQL和DATEDIFF（）

I have been convering the following MS-SQL Server's query to MySQL and I am stuck at this date part. Can anyone please tell me what I am doing wrong here?

我一直在将以下MS-SQL Server的查询转换为MySQL,并且我被困在这个日期部分。谁能告诉我这里我做错了什么?

Original MS-SQL Query (Working)

原始MS-SQL查询(工作)

select  top 4
    [Mon]=sum(case when datename(weekday, vrdate) = 'Monday' then namount else 0 end),
    [Tue]=sum(case when datename(weekday, vrdate) = 'Tuesday' then namount else 0 end),
    [Wed]=sum(case when datename(weekday, vrdate) = 'Wednesday' then namount else 0 end),
    [Thu]=sum(case when datename(weekday, vrdate) = 'Thursday' then namount else 0 end),
    [Fri]=sum(case when datename(weekday, vrdate) = 'Friday' then namount else 0 end),
    [Sat]=sum(case when datename(weekday, vrdate) = 'Saturday' then namount else 0 end),
    [Sun]=sum(case when datename(weekday, vrdate) = 'Sunday' then namount else 0 end)
from    stockmain
where    etype = 'sale' and  DATEDIFF(MONTH, vrdate, getDate()) = 0
group by datepart(week, vrdate)
order by datepart(week, vrdate) ;

Respective MySQL Query (Not Working)

相应的MySQL查询(不工作)

Below is the respective MySQL Query and I am stuck at the DATEDIFF in WHERE clause.

下面是相应的MySQL查询,我被困在WHERE子句中的DATEDIFF。

select 
        sum(case when date_format(vrdate, '%W') = 'Monday' then namount else 0 end) as 'Mon',
        sum(case when date_format(vrdate, '%W') = 'Tuesday' then namount else 0 end) as 'Tue',
        sum(case when date_format(vrdate, '%W') = 'Wednesday' then namount else 0 end) as 'Wed',
        sum(case when date_format(vrdate, '%W') = 'Thursday' then namount else 0 end) as 'Thu',
        sum(case when date_format(vrdate, '%W') = 'Friday' then namount else 0 end) as 'Fri',
        sum(case when date_format(vrdate, '%W') = 'Saturday' then namount else 0 end) as 'Sat',
        sum(case when date_format(vrdate, '%W') = 'Sunday' then namount else 0 end) as 'Sun'
from    stockmain
where    etype = 'sale' and DATEDIFF(MONTH, vrdate, CURDATE()) = 0
group by WEEK(VRDATE)
order by WEEK(VRDATE) desc
LIMIT 4

1 个解决方案

#1

The MySQL equivalent of how datediff works on SQL Server is timestampdiff.

MySQL等效于如何在SQL Server上运行的MySQL是timestampdiff。

WHERE TIMESTAMPDIFF(month, vrdate, curdate()) = 0

The following may be equivalent, depending on how datediff rounds the month before its calculation:

以下内容可能相同,具体取决于计算前一个月的日期:

WHERE MONTH(vrdate) = MONTH(CURDATE())

#1