SQL Server 2008 R2日期转换报错求助：字符转日期/时间失败

嘿，我一眼就定位到问题根源了——这个转换错误完全是因为动态SQL里直接拼接datetime类型变量导致的。当你把@MES_EN_CURSO和@MES_ANTERIOR这类datetime变量直接拼进字符串时，SQL Server会自动把它们转成默认的日期字符串格式（比如Oct 15 2024 12:00:00:000AM），这种格式既没有引号包裹，又不符合CONVERT函数的预期输入，自然会触发类型转换失败。

具体问题点 #

看你代码里的这段逻辑：

SET @SQL2 = ' A.NUM_OPERACION IN ( SELECT B.NUM_CONTRATO FROM ( SELECT NUM_CONTRATO FROM ( SELECT * FROM #OPER WHERE FECHA_INICIO IS NOT NULL) H WHERE H.FECHA_INICIO < CAST(CONVERT(VARCHAR(12),'+@MES_EN_CURSO+',23) AS VARCHAR ) AND H.FECHA_INICIO >= CAST(CONVERT(VARCHAR(12),'+@MES_ANTERIOR+',23) AS VARCHAR ) ) B WHERE B.NUM_CONTRATO IS NOT NULL ) '

实际执行时，这段会被解析成类似下面的无效语法：

H.FECHA_INICIO < CAST(CONVERT(VARCHAR(12),Oct 15 2024 12:00:00:000AM,23) AS VARCHAR )

显然，Oct 15 2024 12:00:00:000AM没有被引号包裹，SQL会把它当成一堆标识符而非日期字符串，转换自然失败。

修改后的可运行代码 #

我已经帮你调整了日期变量的处理逻辑，提前把datetime转成标准的YYYY-MM-DD格式字符串并加上单引号，确保动态SQL执行时语法合法：

WHILE (SELECT TOP 1 FECHA1 FROM #PERIODO) > 0 BEGIN
    SET @MES_ANTERIOR = (SELECT TOP 1 FECHA1 FROM #PERIODO ORDER BY FECHA1 ASC)
    SET @MES_EN_CURSO = (SELECT TOP 1 B.FECHA1 FROM (SELECT TOP 2 A.FECHA1 FROM #PERIODO A ORDER BY FECHA1 ASC ) B ORDER BY B.FECHA1 DESC)
    SET @MES_3 = (SELECT TOP 1 FECHA3 FROM #PERIODO ORDER BY FECHA1 ASC)
    SET @MES_4 = (SELECT TOP 1 B.FECHA3 FROM (SELECT TOP 2 A.FECHA3 FROM #PERIODO A ORDER BY FECHA3 ASC ) B ORDER BY B.FECHA3 DESC)
    SET @MES_ANT = LEFT(CONVERT(VARCHAR(8),@MES_ANTERIOR,112),8)
    -- SELECT @MES_ANT
    SET @MES_CURR = LEFT(CONVERT(VARCHAR(8),@MES_EN_CURSO,112),8)
    -- SELECT @MES_CURR
    EXEC('IF OBJECT_ID(''WORK.DBO.OPERACIONES_ABIF_'+@MES_CURR+''', ''U'') IS NOT NULL DROP TABLE WORK.DBO.OPERACIONES_ABIF_'+@MES_CURR+' ')
    
    -- 提前将datetime变量转换为带单引号的标准日期字符串
    DECLARE @MES_EN_CURSO_STR VARCHAR(10) = QUOTENAME(CONVERT(VARCHAR(10), @MES_EN_CURSO, 23), '''')
    DECLARE @MES_ANTERIOR_STR VARCHAR(10) = QUOTENAME(CONVERT(VARCHAR(10), @MES_ANTERIOR, 23), '''')

    SET @SQL1 = 'SELECT DISTINCT A.* INTO WORK.DBO.OPERACIONES_ABIF_'+@MES_CURR+' FROM BDGESTION.DBO.BASE_RIESGOS_PROD_ALTAIR_'+@MES_CURR+' A WHERE '
    -- 直接使用转换好的日期字符串变量，无需再嵌套CAST/CONVERT
    SET @SQL2 = ' A.NUM_OPERACION IN ( SELECT B.NUM_CONTRATO FROM ( SELECT NUM_CONTRATO FROM ( SELECT * FROM #OPER WHERE FECHA_INICIO IS NOT NULL) H WHERE H.FECHA_INICIO < '+@MES_EN_CURSO_STR+' AND H.FECHA_INICIO >= '+@MES_ANTERIOR_STR+' ) B WHERE B.NUM_CONTRATO IS NOT NULL ) '
    EXEC (@SQL1 + @SQL2)
    DELETE FROM #PERIODO WHERE FECHA1 = @MES_ANTERIOR
END

关键修改说明 #

• 新增了@MES_EN_CURSO_STR和@MES_ANTERIOR_STR变量：用CONVERT(VARCHAR(10), @变量, 23)得到YYYY-MM-DD格式的字符串，再用QUOTENAME(..., '''')给字符串加上单引号，确保动态SQL里的日期值是合法的字符串。
• 简化了@SQL2里的条件判断：直接使用转换好的日期字符串，不需要再嵌套CAST(CONVERT(...))，既提高可读性又避免转换错误。

额外建议 #

如果后续要优化代码，建议考虑使用参数化动态SQL（比如sp_executesql），可以避免SQL注入风险，同时让日期类型的处理更稳定——不过既然你要求保留现有循环逻辑，上面的修改已经能解决当前的转换错误，让代码正常运行。

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