SQL Server存储过程中字符串转DateTime更新表失败求助

场景说明 #

• 目标表[Table_Input]的fechaInicio字段为datetime类型，初始值为NULL
• 单独执行SELECT CAST('08/25/2022 03:34:59 PM' AS DATETIME)可成功完成转换
• 调用存储过程[SP_Table_Input_Get_Series]传入相同字符串时，触发报错：

Msg 241, Level 16, State 1, Procedure SP_Table_Input_Get_Series, Line 34 [Batch Start Line 13]
Conversion failed when converting date and/or time from character string

存储过程原代码：

PROCEDURE [SP_Table_Input_Get_Series]
    @strDateTime NVARCHAR(50),
    @cId int
AS
BEGIN TRANSACTION
    UPDATE [Table_Input]
    SET 
       ---fechaInicio =convert(datetime, @strDateTime, 5),
       ---fechaInicio = N'select cast(@strDateTime as datetime)'
       fechaInicio = CAST(@strDateTime AS datetime)
    WHERE id = @cId     -- the where clause works fine

    COMMIT TRANSACTION

调用方式：

[SP_Table_Input_Get_Series] '08/25/2022 03:34:59 PM', 2

原因分析 #

问题源于会话日期格式设置的差异：单独执行查询时，你的会话DATEFORMAT可能为mdy（月/日/年），但存储过程执行时的会话设置可能是dmy（日/月/年），导致SQL Server尝试将08/25/2022解析为“第25个月”，因逻辑无效触发转换错误。

解决方案 #

方案1：使用带明确样式的CONVERT函数 #

你的日期格式为MM/DD/YYYY HH:MI:SS AM/PM，对应SQL Server转换样式码101（美式日期格式，兼容带时间的字符串），修改存储过程的SET语句：

fechaInicio = CONVERT(datetime, @strDateTime, 101)

方案2：在存储过程中显式设置DATEFORMAT #

在事务开始前强制指定会话日期格式为mdy，确保解析逻辑统一：

PROCEDURE [SP_Table_Input_Get_Series]
    @strDateTime NVARCHAR(50),
    @cId int
AS
SET DATEFORMAT mdy; -- 强制按「月/日/年」解析日期字符串
BEGIN TRANSACTION
    UPDATE [Table_Input]
    SET 
       fechaInicio = CAST(@strDateTime AS datetime)
    WHERE id = @cId

    COMMIT TRANSACTION

方案3：使用TRY_CONVERT做安全转换（可选） #

如果需要兼容可能的非法日期输入，避免存储过程报错中断，可使用TRY_CONVERT，转换失败时返回NULL：

fechaInicio = TRY_CONVERT(datetime, @strDateTime, 101)

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