SQL Server 2012存储过程转置表时遇日期转换字符串失败错误

你碰到的这个「Conversion failed when converting date and/or time from character string」错误，核心问题出在动态SQL拼接时直接把日期变量和整数变量硬塞进字符串里，SQL Server没法正确识别这些变量的字符串表示，自然就报错了。我帮你拆解问题并给出两种解决方案，优先推荐更安全的参数化方式：

问题根源分析 #

看你原来的代码，有几个明显的问题：

• 拼接@fromDate和@toDate时，直接把datetime变量拼进SQL字符串，结果会变成类似convert(date,2024-05-20 00:00:00,105)的写法——日期没有用单引号包裹，SQL会把它当成数值表达式计算，肯定会转换失败。
• 还有个小拼写错误：DISTCINT应该是DISTINCT，这个虽不会直接导致当前错误，但可能让列名重复，影响转置结果。
• 另外你WHERE条件里用了OR，大概率逻辑不符合报表需求（一般是要同时满足日期范围和指定地点），我后面会帮你调整，要是你确实需要OR逻辑可以改回去。

推荐解决方案：用参数化执行动态SQL（安全可靠） #

这种方式完全避免变量拼接的类型问题，还能防止SQL注入，是最佳实践。修正后的存储过程代码如下：

CREATE PROCEDURE [dbo].[sp_Report_SalesJournal] 
    (@fromDate DATETIME, 
     @toDate DATETIME, 
     @locationId INT) 
AS 
BEGIN
    SET NOCOUNT ON; -- 避免返回额外的计数结果，干扰报表输出

    DECLARE @cols AS NVARCHAR(MAX) = '';
    DECLARE @query AS NVARCHAR(MAX) = '';

    -- 拼接转置需要的列名，修正DISTINCT拼写错误
    SELECT @cols = @cols + QUOTENAME(AccountName) + ',' 
    FROM (SELECT DISTINCT AccountName FROM vw_SalesJournal ) AS tmp;

    -- 移除最后一列末尾的多余逗号
    SELECT @cols = SUBSTRING(@cols, 0, LEN(@cols));

    -- 构建动态SQL，用占位符代替实际变量
    SET @query = N'
        SELECT * 
        FROM (
            SELECT 
                InvoiceDate, 
                TransactionNumber, 
                CustomerName, 
                Amount, 
                AccountName 
            FROM vw_SalesJournal 
            WHERE 
                InvoiceDate BETWEEN @p_fromDate AND @p_toDate
                AND LocationId = @p_locationId -- 这里改成AND，符合常规报表筛选逻辑，需要OR的话自行修改
        ) src 
        PIVOT (
            MAX(Amount) 
            FOR AccountName IN (' + @cols + ')
        ) piv';

    -- 使用sp_executesql传递参数并执行动态SQL
    EXEC sp_executesql 
        @query, 
        N'@p_fromDate DATETIME, @p_toDate DATETIME, @p_locationId INT',
        @p_fromDate = @fromDate,
        @p_toDate = @toDate,
        @p_locationId = @locationId;
END

关键调整点： #

1. 用sp_executesql的参数化方式传递变量，SQL Server会自动处理类型转换，彻底解决日期转换错误。
2. 修正了DISTCINT的拼写错误，保证列名唯一。
3. 加上SET NOCOUNT ON;优化存储过程输出。
4. 调整WHERE条件为AND，符合大多数报表的筛选逻辑，若你确实需要「日期范围内 或 指定地点」的逻辑，改回OR即可。

备选方案：直接拼接字符串（不推荐，有SQL注入风险） #

如果你因为某些原因必须直接拼接字符串，那要把日期转成带单引号的字符串格式，整数变量也要转成字符串：

CREATE PROCEDURE [dbo].[sp_Report_SalesJournal] (@fromDate DATETIME, @toDate DATETIME, @locationId INT) AS 
BEGIN
    DECLARE @cols AS NVARCHAR(MAX) = '';
    DECLARE @query AS NVARCHAR(MAX) = '';

    -- 修正DISTINCT拼写错误
    SELECT @cols = @cols + QUOTENAME(AccountName) + ',' FROM (SELECT DISTINCT AccountName FROM vw_SalesJournal ) AS tmp
    SELECT @cols = SUBSTRING(@cols, 0, LEN(@cols))

    -- 把日期转成带单引号的字符串，整数转成字符串再拼接
    SET @query = 'SELECT * from ( 
        select InvoiceDate, TransactionNumber, CustomerName, Amount, AccountName 
        from vw_SalesJournal 
        Where (InvoiceDate BETWEEN convert(date,''' + CONVERT(NVARCHAR(10), @fromDate, 105) + ''',105) 
            AND convert(date,''' + CONVERT(NVARCHAR(10), @toDate, 105) + ''',105)) 
            AND LocationId=' + CAST(@locationId AS NVARCHAR(10)) + ' 
    ) src pivot ( max(Amount) for AccountName in (' + @cols + ') ) piv'
    
    EXEC(@query)
END

关键调整点： #

• 用CONVERT(NVARCHAR(10), @fromDate, 105)把日期转成dd-mm-yyyy格式的字符串，再用两个单引号包裹（SQL字符串里两个单引号表示一个实际单引号），这样拼接后的SQL里日期是合法的字符串格式。
• 把@locationId转成字符串后再拼接，避免类型不匹配问题。

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