SQL Server存储过程报varchar转datetime错误如何处理

报错由两处不严谨的日期类型用法共同触发：

• 入参类型不匹配：传入的时间字符串'2022-06-02 14:59:04.4250000'带7位小数秒，而存储过程、函数中定义的@WorkingDate为传统datetime类型，该类型最高仅支持3位小数秒（精度约1/300秒），在会话日期格式、语言设置非默认的场景下，7位精度的时间字符串隐式转换为datetime时会直接失败。
• 隐式转换风险：fn_wl_CalculateWorklistItemGroupTypeCurrentLoad函数中@WorkingDate + '23:59:59.997'的写法，是将日期类型和字符串直接做算术运算，SQL Server会尝试把字符串隐式转为日期类型，一旦会话的DATEFORMAT设置和字符串格式不匹配就会抛错；两个函数中IF ISNULL(@WorkingDate, -1) = -1的判断逻辑，是让日期类型和整数做隐式转换，依赖SQL Server默认日期基准值，也存在判断失效、转换报错的隐患。

1. 统一日期参数类型 #

将存储过程、两个自定义函数中的@WorkingDate参数类型从datetime改为datetime2(3)（需要保留7位时间精度则改为datetime2(7)）。datetime2是SQL Server 2008及以上版本支持的ISO合规日期类型，对各类时间字符串格式兼容性远高于旧版datetime，支持自定义0-7位小数秒精度，可直接兼容传入的7位小数时间字符串。

2. 替换所有不安全的隐式转换写法 #

• 把两个函数中判断入参是否为空的逻辑，从IF ISNULL(@WorkingDate, -1) = -1改为标准的IF @WorkingDate IS NULL，避免日期和整数的隐式转换。
• 废弃用字符串拼接当日结束时间的写法，改用>=和<的半开区间查询全天数据，彻底消除字符串转日期的风险，也不会出现时间精度遗漏的问题。

3. 修正后可直接运行的代码 #

存储过程定义 #

ALTER PROCEDURE [dbo].[sp_wl_CalculateWorklistItemGroupTypeCurrentStats] (
  @Practice_ID bigint,
  @WorkingDate datetime2(3) = NULL,
  @All bit = 0
)
AS 
  SELECT 
    WLIT.WorklistItemGroupTypeIdentify as ID,
    WLIT.Name as Prefix, 
    dbo.fn_wl_CalculateWorklistItemGroupTypeCurrentLoad(@Practice_ID, WLIT.WorklistItemGroupTypeIdentify, @WorkingDate) as CurrentLoad,
    dbo.fn_wl_CalculateWorklistItemGroupTypeCurrentCapacity(@Practice_ID, WLIT.WorklistItemGroupTypeIdentify, @WorkingDate ) as MaxCapacity
  FROM WorklistItemGroupType WLIT
  WHERE WLIT.PracticeIdentify = @Practice_ID
  AND WLIT.ReportOn = CASE @All WHEN 1 THEN WLIT.ReportOn ELSE 1 END

容量计算函数 #

ALTER FUNCTION [dbo].[fn_wl_CalculateWorklistItemGroupTypeCurrentCapacity] (
  @Practice_ID bigint,
  @GroupType_ID int,
  @WorkingDate datetime2(3) = NULL
)
RETURNS INT
AS
BEGIN 
  DECLARE @GlobalCapacity int = 0, @DayCapacity int = 0, @InstanceCapacity int = 0, @Result int = -1

  IF @WorkingDate IS NULL
    SET @WorkingDate = GETDATE()

  SET @WorkingDate = CAST(@WorkingDate as DATE)

  SELECT @GlobalCapacity = W.MaxCapacity, 
    @DayCapacity = CASE (@@datefirst - 1 + datepart(weekday, @WorkingDate)) % 7
      WHEN 0 THEN SundayCapacity
      WHEN 1 THEN MondayCapacity
      WHEN 2 THEN TuesdayCapacity
      WHEN 3 THEN WednesdayCapacity
      WHEN 4 THEN ThursdayCapacity
      WHEN 5 THEN FridayCapacity
      WHEN 6 THEN SaturdayCapacity
    END,
    @InstanceCapacity = COALESCE(Ext.MaxCapacity, 0)
  FROM WorklistItemGroupType W
  LEFT OUTER JOIN (SELECT TOP 1 WLIGTIdentify, MaxCapacity
                   FROM WorklistItemGroupTypeExtension 
                   WHERE WLIGTIdentify = @GroupType_ID
                   AND @WorkingDate BETWEEN TargetDateStart AND TargetDateEnd) as Ext ON (W.WorklistItemGroupTypeIdentify = Ext.WLIGTIdentify)
  WHERE WorklistItemGroupTypeIdentify = @GroupType_ID
  AND PracticeIdentify = @Practice_ID

  IF @InstanceCapacity > 0
    SET @Result = @InstanceCapacity
  ELSE IF @DayCapacity > 0
    SET @Result = @DayCapacity
  ELSE IF @GlobalCapacity > 0
    SET @Result = @GlobalCapacity;

  RETURN @Result
END

负载计算函数 #

ALTER FUNCTION [dbo].[fn_wl_CalculateWorklistItemGroupTypeCurrentLoad] (
  @Practice_ID bigint,
  @GroupType_ID int,
  @WorkingDate datetime2(3) = NULL
)
RETURNS INT
AS
BEGIN 
  DECLARE @CurrentLoad int = -1
  DECLARE @DateStart date, @DateNextStart date

  IF @WorkingDate IS NULL
    SET @WorkingDate = GETDATE()

  SET @DateStart = CAST(@WorkingDate as DATE)
  SET @DateNextStart = DATEADD(DAY, 1, @DateStart)
  
  SELECT @CurrentLoad = COUNT(WLI.WorkListItemIdentify)
  FROM WorkListItem WLI
  INNER JOIN WorklistItemType WLIT ON (WLI.ItemTypeIdentify = WLIT.WorklistItemTypeIdentify) AND (WLIT.GroupTypeIdentify = @GroupType_ID)
  WHERE WLI.DateCreated >= @DateStart AND WLI.DateCreated < @DateNextStart
  AND WLI.PracticesIdentify = @Practice_ID
  AND WLI.Deleted = 0

  RETURN @CurrentLoad
END

• 所有日期运算优先使用DATEADD、DATEDIFF等内置日期函数实现，不要直接将字符串和日期类型做算术拼接，从根源避免隐式转换报错。
• 硬编码日期常量时优先使用ISO 8601格式（如'2022-06-02T14:59:04'，日期和时间中间加T），不受会话级日期格式、语言设置影响，转换不会失败。

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