存储过程中日期区间数据统计及SQL语法错误修正求助

嗨，你的问题确实出在WHERE子句的BETWEEN语法上——正确的语法结构应该是 [字段名] BETWEEN [起始值] AND [结束值]，你把字段和BETWEEN的位置搞反了，导致SQL解析报错。

修正后的完整存储过程代码如下：

CREATE PROCEDURE sp_per_transaction 
    @Datefrom DATETIME, 
    @Dateto DATETIME 
AS 
BEGIN
    SELECT 
        Trans,
        SUM(ISNULL(DI_SC, 0)) AS "Sum of SC",
        SUM(ISNULL(DI_PWD, 0)) AS "Sum of PWD",
        SUM(ISNULL(DI_Student, 0)) AS "Sum of Student",
        SUM(ISNULL(VATable, 0)) AS "Sum of VATable",
        SUM(ISNULL(VAT, 0)) AS "Sum of VAT",
        SUM(ISNULL(VATExempt, 0)) AS "Sum of VATExempt",
        SUM(ISNULL(GrossAmount, 0)) AS "Sum of GrossAmount"
    FROM 
        [ATIBTicketingDB_test2].[dbo].[Transaction]
    WHERE 
        DatePosted BETWEEN @Datefrom AND @Dateto
    GROUP BY 
        Trans;
END

额外小优化说明： #

• 我把代码做了格式化，让结构更清晰，方便后续维护
• 去掉了SUM和括号之间的空格，这不是语法错误，但属于更规范的SQL写法
• 你已经用方括号把Transaction表名括起来了，这点做得很棒——因为Transaction是SQL的保留关键字，必须用方括号转义才能作为表名使用

内容的提问来源于stack exchange，提问作者Reden Rodriguez