如何解决SQL Server表值函数执行时“从字符串转换日期/时间失败”错误

你遇到的转换错误是两个语法问题共同触发的：

• 列插入顺序不匹配：你INSERT语句声明的列顺序是Travel,StatusTravel,DATEENTER,IdStatusTravel，但SELECT语句的输出顺序是Travel、DATEENTER、StatusTravel、IdStatusTravel，相当于把DATEENTER的datetime值插入到varchar类型的StatusTravel字段，把字符串类型的行程状态值插入到datetime类型的DATEENTER字段，必然触发类型转换错误。
• DATEPART参数错误：DATEPART(YearTravel, V.DATEENTER)写法错误，DATEPART第一个参数是固定的日期部分标识，你错误传入了自定义参数名YearTravel，应该改为合法的年标识YEAR。

直接修正上述两个错误即可，若你源表TblTravel的DATEENTER本身是字符串存储的非标准格式，可以额外加TRY_CONVERT处理无效日期值。

修正后的函数代码 #

CREATE FUNCTION [dbo].[Function_TotalTravel](
    @YearTravel SMALLINT = 0,
    @MonthTravel SMALLINT = 0
)
RETURNS @Tabla TABLE (
    Travel VARCHAR(20),
    StatusTravel VARCHAR(20),
    DATEENTER DATETIME,
    IdStatusTravel INT
)
AS
BEGIN

INSERT INTO @Tabla (Travel,StatusTravel,DATEENTER,IdStatusTravel)
SELECT  V.Travel,
        -- 调整顺序：第二个值对应StatusTravel字段
        CASE    WHEN V.TravelIncomplete = 1 THEN 'Travel Incomplete'
                ELSE CASE 
                WHEN V.StatusTravel = 2 THEN 'Transit'
                WHEN V.StatusTravel = 3 THEN 'Closed'
                WHEN V.StatusTravel = 4 THEN 'Cancel'
                ELSE CAST(V.StatusTravel AS VARCHAR)
                END END as StatusTravel,
        -- 调整顺序：第三个值对应DATEENTER字段，若源字段是字符串可以换成 TRY_CONVERT(DATETIME, V.DATEENTER, 120) 等适配你的日期格式
        V.DATEENTER,
        CASE    WHEN V.TravelIncomplete = 1 THEN 99
        ELSE    V.StatusTravel END IdStatusTravel
        
FROM    TblTravel V WITH(NOLOCK)
-- 修正DATEPART第一个参数为YEAR
WHERE   (DATEPART(YEAR, V.DATEENTER) = @YearTravel OR @YearTravel = 0)
AND     (DATEPART(MONTH, V.DATEENTER) = @MonthTravel OR @MonthTravel = 0)
AND  ((V.StatusTravel NOT IN (0,1) AND V.TravelIncomplete = 0) OR V.TravelIncomplete = 1)

RETURN
END

额外注意事项 #

如果源表TblTravel.DATEENTER本身是varchar等字符串类型而非datetime类型，建议：

1. 优先修改表结构，将日期字段改为datetime/datetime2类型，避免脏数据
2. 暂时无法改表的话，把V.DATEENTER替换为TRY_CONVERT(DATETIME, V.DATEENTER, 格式码)，格式码根据你的日期字符串规则选择，比如120对应yyyy-mm-dd hh:mi:ss格式，转换失败会返回NULL不会触发报错。

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