SQL Server存储过程关联数据集后nvarchar转datetime报错求助

错误原因与修复方案 #

核心报错触发原因 #

Error converting data type nvarchar to datetime报错是非安全的日期隐式转换导致的：你在SSMS本地测试时，会话默认日期格式是MDY（月/日/年），所以字符串转datetime成功；但报表服务的数据库会话默认日期格式不同，字符串转datetime失败。

代码里的具体问题点 #

• 无效的存储过程定义：创建存储过程的第一行，参数声明前多了冗余的'11/14/2021'，属于语法错误
• 传入参数未使用：你定义了入参@ReportDate，但代码里所有逻辑都用的是硬编码的局部变量@ReportDates，入参完全失效
• 不安全的日期转换：
  1. @BeginOfYear赋值直接把拼接的'1/1/xxxx'字符串隐式转datetime，完全依赖会话日期格式
  2. @BeginOfMonth赋值拼接月/1/年字符串后再转datetime，同样存在格式依赖问题
• 潜在的周计算逻辑错误：用DATENAME(dw, @ReportDates)判断星期几，返回值随服务器语言设置变化，中文环境会返回星期日等中文值，判断Sunday永远不成立，周起始日期计算错误

修复后的完整代码 #

CREATE PROCEDURE [dbo].[proc_LoansProcessed_ToDate]
    @ReportDate Datetime,
    @LoanAmount varchar(max) = NULL,
    @LoanPurpose varchar(max) = NULL,
    @PropertyUsage varchar(max) = NULL,
    @Demographics2 varchar(max) = NULL
AS
BEGIN
    -- 直接使用传入的参数，不需要硬编码
    DECLARE @DayOfWeek varchar(100),
            @BeginOfWeek Datetime,
            @BeginOfMonth Datetime,
            @BeginOfYear Datetime

    -- 用DATEPART计算周几，不依赖语言设置，1=周日，2=周一...7=周六（美国标准，可根据需求调整偏移）
    SET @BeginOfWeek = DATEADD(dd, 1 - DATEPART(dw, @ReportDate), @ReportDate)
    -- 安全计算月初，不需要字符串拼接
    SET @BeginOfMonth = DATEFROMPARTS(YEAR(@ReportDate), MONTH(@ReportDate), 1)
    -- 安全计算年初，不需要字符串拼接
    SET @BeginOfYear = DATEFROMPARTS(YEAR(@ReportDate), 1, 1)

    SELECT      
        b.*,
        d.[martial status], d.Sex, d.Race,
        DATEDIFF(YY, d.[date of birth], @ReportDate) Age,
        [borrower first name] + ' ' + [borrower last name] BorrowerName,
        [monthly income],
        DATENAME(MONTH, [loan date]) + ' ' + CONVERT(VARCHAR(10), DATEPART(YY, [loan date])) FormatedReportMonth,
        CASE 
            WHEN [loan date] >= @BeginOfWeek AND [loan date] <= @ReportDate 
                THEN 'WeekToDate'
            WHEN [loan date] >= @BeginOfMonth AND [loan date] <= @ReportDate  
                THEN 'MonthToDate'
            WHEN [loan date] >= @BeginOfYear AND [loan date] <= @ReportDate 
                THEN 'YearToDate' 
            ELSE NULL 
        END ToDate,
        CASE 
            WHEN [loan date] >= @BeginOfWeek AND [loan date] <= @ReportDate 
                THEN 1
            WHEN [loan date] >= @BeginOfMonth AND [loan date] <= @ReportDate  
                THEN 2
            WHEN [loan date] >= @BeginOfYear AND [loan date] <= @ReportDate 
                THEN 3 
            ELSE NULL 
        END ToDateOrder,
        CASE 
            WHEN [loan amount] <= 100000 THEN 'Less Than $100k'
            WHEN [loan amount] between 100000 AND 200000 THEN '$100k to $200k'
            WHEN [loan amount] > 200000 THEN 'More Than $200k' 
        END LoanAmountGroup,
        CASE 
            WHEN [loan amount] <= 100000 THEN 1
            WHEN [loan amount] between 100000 AND 200000 THEN 2
            WHEN [loan amount] > 200000 THEN 3 
        END LoanAmountGroupOrder,
        CASE 
            WHEN DATEDIFF(YY, d.[date of birth], @ReportDate) < 25 THEN '<=25'
            WHEN DATEDIFF(YY, d.[date of birth], @ReportDate) BETWEEN 25 AND 35 THEN '26-35'
            WHEN DATEDIFF(YY, d.[date of birth], @ReportDate) BETWEEN 36 AND 45 THEN '36-45'   
            WHEN DATEDIFF(YY, d.[date of birth], @ReportDate) > 45 THEN '46+' 
        END AgeGroup,
        CASE 
            WHEN DATEDIFF(YY, d.[date of birth], @ReportDate) < 25 THEN 1
            WHEN DATEDIFF(YY, d.[date of birth], @ReportDate) BETWEEN 25 AND 35 THEN 2
            WHEN DATEDIFF(YY, d.[date of birth], @ReportDate) BETWEEN 36 AND 45 THEN 3
            WHEN DATEDIFF(YY, d.[date of birth], @ReportDate) > 45 THEN 4 
        END AgeGroupOrder   
    FROM        
        [dbo].[fact_financials] a
    LEFT JOIN   
        [dbo].[dim_loan info] b ON a.[loan ID] = b.[loan ID]
    LEFT JOIN   
        [dbo].[dim_property info] c ON a.[loan ID] = c.[loan ID]
    LEFT JOIN   
        [dbo].[dim_borrower info] d ON a.[loan ID] = d.[loan ID]
    WHERE       
        [loan date] <= @ReportDate
END

额外注意点 #

如果你的周起始是周一而非周日，调整@BeginOfWeek的计算逻辑为：

SET DATEFIRST 1;
SET @BeginOfWeek = DATEADD(dd, 1 - DATEPART(dw, @ReportDate), @ReportDate);

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