仅在MERGE语句中出现日期时间转换失败问题求助

API 探路者

2026-7-10

问题：日期字符串转换失败的排查与解决

我运行存储过程将staging表数据转换后合并到fact表时，遇到错误：Conversion failed when converting date and/or time from character string。但执行失败后检查staging表，所有非NULL日期值都已正确转换为yyyy-MM-dd HH:mm:ss格式，单独执行相同转换逻辑的SELECT查询也无错误，推测问题出在存储过程的执行逻辑中。

表结构

Staging表结构

[staging].[SeedStoredLocalBags](
    [LocCode] [varchar](15) NULL,
    [SessionID] [varchar](22) NULL,
    [TimeStored] [varchar](55) NULL,
    [TimeRemoved] [varchar](55) NULL,
    [StoredBy] [varchar](15) NULL,
    [RemovedBy] [varchar](15) NULL,
    [RemovalMethod] [tinyint] NULL,
    [RemovalMethodDesc] [varchar](255) NULL,
    [AnalystRef] [int] NULL
)

Fact表结构

[pmr].[StoredLocalBag](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [LocationId] [varchar](15) NOT NULL,
    [Session] [nvarchar](22) NULL,
    [TimeStored] [datetime2](0) NULL,
    [TimeRemoved] [datetime2](0) NULL,
    [StoredBy] [varchar](15) NULL,
    [RemovedBy] [varchar](15) NULL,
    [RemovalMethod] [tinyint] NULL,
    [DeletedAt] [datetime2](0) NULL
)

存储过程核心代码

PROCEDURE [staging].[StoredLocalBagsProc]
    @AnalystRef int
AS       
BEGIN
    SET NOCOUNT ON;            
    DECLARE @BranchNumber varchar(4)
    SELECT @BranchNumber = CAST(Branch AS varchar(4)) FROM pmr.AnalystLicense WHERE PSLAccountCode = @AnalystRef
    BEGIN
        UPDATE staging.StoredLocalBags
        SET LocCode = @BranchNumber + '-' + LocCode
        WHERE AnalystRef = @AnalystRef;
        UPDATE staging.StoredLocalBags
        SET TimeStored = NULL
        WHERE AnalystRef = @AnalystRef AND TimeStored = '';
        UPDATE staging.StoredLocalBags
        SET [TimeStored] = SUBSTRING([TimeStored],7,4) + '-' + SUBSTRING([TimeStored],4,2) + '-' + SUBSTRING([TimeStored],1,2) + ' ' + SUBSTRING([TimeStored],12,2) + ':' + SUBSTRING([TimeStored],15,2) + ':'  + SUBSTRING([TimeStored],18,2)
        WHERE AnalystRef = @AnalystRef AND [TimeStored] IS NOT NULL; 
        UPDATE staging.StoredLocalBags
        SET TimeRemoved = NULL
        WHERE AnalystRef = @AnalystRef AND TimeRemoved = '';
        UPDATE staging.StoredLocalBags
        SET [TimeRemoved] = SUBSTRING([TimeRemoved],7,4) + '-' + SUBSTRING([TimeRemoved],4,2) + '-' + SUBSTRING([TimeRemoved],1,2) + ' ' + SUBSTRING([TimeRemoved],12,2) + ':' + SUBSTRING([TimeRemoved],15,2) + ':'  + SUBSTRING([TimeRemoved],18,2)
        WHERE AnalystRef = @AnalystRef AND [TimeRemoved] IS NOT NULL;             
    END
    MERGE pmr.StoredLocalBag AS tgt
    USING staging.StoredLocalBags as src
    ON (tgt.LocationId = src.LocCode AND tgt.Session = src.SessionID AND tgt.TimeStored = src.TimeStored)
    WHEN MATCHED AND src.AnalystRef = @AnalystRef THEN 
        UPDATE SET                            
            TimeStored = src.[TimeStored],
            TimeRemoved = src.[TimeRemoved],
            StoredBy = src.StoredBy,
            RemovedBy = src.RemovedBy,
            RemovalMethod = src.[RemovalMethod]
    WHEN NOT MATCHED AND src.AnalystRef = @AnalystRef THEN
        INSERT ([LocationId],[Session],[TimeStored],[TimeRemoved],[StoredBy],[RemovedBy],[RemovalMethod],[DeletedAt])
        VALUES (
            src.LocCode,                      
            src.[SessionID],
            src.[TimeStored],
            src.[TimeRemoved],
            src.StoredBy,
            src.RemovedBy,
            src.[RemovalMethod],
            NULL
        );                         
END;

数据示例

转换前的日期数据

TimeStored	TimeRemoved
19/09/2023 16:50:00	20/09/2023 12:05:00
19/09/2023 17:25:00	NULL
15/09/2023 09:15:00	18/09/2023 14:30:00

报错后staging表的转换后数据

TimeStored	TimeRemoved
2023-09-19 16:50:00	2023-09-20 12:05:00
2023-09-19 17:25:00	NULL
2023-09-15 09:15:00	2023-09-18 14:30:00

问题原因

核心原因是SQL Server执行计划的优化逻辑：虽然代码中先执行staging表的UPDATE转换，再执行MERGE，但SQL Server可能会提前读取MERGE的源数据（staging表的原始未转换数据），再执行UPDATE。此时MERGE的ON条件中，tgt.TimeStored是datetime2类型，src.TimeStored是原始的dd/MM/yyyy HH:mm:ss字符串，SQL Server会尝试隐式转换该字符串为datetime2，但不同语言环境下的日期格式解析规则可能导致转换失败（比如默认语言是美式英语时，会把19/09/2023解析为月/日/年，19作为月份显然无效）。

解决办法

方案1：使用临时表存储转换后的数据

先将转换后的staging数据存入临时表，再用临时表作为MERGE的源，彻底避免执行计划的顺序问题：

PROCEDURE [staging].[StoredLocalBagsProc]
    @AnalystRef int
AS       
BEGIN
    SET NOCOUNT ON;            
    DECLARE @BranchNumber varchar(4)
    SELECT @BranchNumber = CAST(Branch AS varchar(4)) FROM pmr.AnalystLicense WHERE PSLAccountCode = @AnalystRef

    -- 创建临时表存储转换后的数据
    CREATE TABLE #TempStoredLocalBags (
        [LocCode] [varchar](15) NULL,
        [SessionID] [varchar](22) NULL,
        [TimeStored] [varchar](55) NULL,
        [TimeRemoved] [varchar](55) NULL,
        [StoredBy] [varchar](15) NULL,
        [RemovedBy] [varchar](15) NULL,
        [RemovalMethod] [tinyint] NULL,
        [AnalystRef] [int] NULL
    )

    -- 复制并转换目标数据到临时表
    INSERT INTO #TempStoredLocalBags
    SELECT 
        @BranchNumber + '-' + LocCode,
        SessionID,
        CASE 
            WHEN TimeStored = '' THEN NULL 
            ELSE SUBSTRING([TimeStored],7,4) + '-' + SUBSTRING([TimeStored],4,2) + '-' + SUBSTRING([TimeStored],1,2) + ' ' + SUBSTRING([TimeStored],12,2) + ':' + SUBSTRING([TimeStored],15,2) + ':'  + SUBSTRING([TimeStored],18,2)
        END AS TimeStored,
        CASE 
            WHEN TimeRemoved = '' THEN NULL 
            ELSE SUBSTRING([TimeRemoved],7,4) + '-' + SUBSTRING([TimeRemoved],4,2) + '-' + SUBSTRING([TimeRemoved],1,2) + ' ' + SUBSTRING([TimeRemoved],12,2) + ':' + SUBSTRING([TimeRemoved],15,2) + ':'  + SUBSTRING([TimeRemoved],18,2)
        END AS TimeRemoved,
        StoredBy,
        RemovedBy,
        RemovalMethod,
        AnalystRef
    FROM staging.StoredLocalBags
    WHERE AnalystRef = @AnalystRef;

    -- 使用临时表执行MERGE
    MERGE pmr.StoredLocalBag AS tgt
    USING #TempStoredLocalBags as src
    ON (tgt.LocationId = src.LocCode AND tgt.Session = src.SessionID AND tgt.TimeStored = TRY_CONVERT(datetime2(0), src.TimeStored))
    WHEN MATCHED THEN 
        UPDATE SET                            
            TimeStored = TRY_CONVERT(datetime2(0), src.[TimeStored]),
            TimeRemoved = TRY_CONVERT(datetime2(0), src.[TimeRemoved]),
            StoredBy = src.StoredBy,
            RemovedBy = src.RemovedBy,
            RemovalMethod = src.[RemovalMethod]
    WHEN NOT MATCHED THEN
        INSERT ([LocationId],[Session],[TimeStored],[TimeRemoved],[StoredBy],[RemovedBy],[RemovalMethod],[DeletedAt])
        VALUES (
            src.LocCode,                      
            src.[SessionID],
            TRY_CONVERT(datetime2(0), src.[TimeStored]),
            TRY_CONVERT(datetime2(0), src.[TimeRemoved]),
            src.StoredBy,
            src.RemovedBy,
            src.[RemovalMethod],
            NULL
        );

    DROP TABLE #TempStoredLocalBags;                        
END;

方案2：在MERGE中嵌套转换逻辑，显式控制转换顺序

直接在MERGE的USING子句中完成数据转换，确保转换后的数据才参与比较和插入：

PROCEDURE [staging].[StoredLocalBagsProc]
    @AnalystRef int
AS       
BEGIN
    SET NOCOUNT ON;            
    DECLARE @BranchNumber varchar(4)
    SELECT @BranchNumber = CAST(Branch AS varchar(4)) FROM pmr.AnalystLicense WHERE PSLAccountCode = @AnalystRef

    -- 直接在MERGE的源中完成转换
    MERGE pmr.StoredLocalBag AS tgt
    USING (
        SELECT 
            @BranchNumber + '-' + LocCode AS LocCode,
            SessionID,
            CASE 
                WHEN TimeStored = '' THEN NULL 
                ELSE SUBSTRING([TimeStored],7,4) + '-' + SUBSTRING([TimeStored],4,2) + '-' + SUBSTRING([TimeStored],1,2) + ' ' + SUBSTRING([TimeStored],12,2) + ':' + SUBSTRING([TimeStored],15,2) + ':'  + SUBSTRING([TimeStored],18,2)
            END AS TimeStored,
            CASE 
                WHEN TimeRemoved = '' THEN NULL 
                ELSE SUBSTRING([TimeRemoved],7,4) + '-' + SUBSTRING([TimeRemoved],4,2) + '-' + SUBSTRING([TimeRemoved],1,2) + ' ' + SUBSTRING([TimeRemoved],12,2) + ':' + SUBSTRING([TimeRemoved],15,2) + ':'  + SUBSTRING([TimeRemoved],18,2)
            END AS TimeRemoved,
            StoredBy,
            RemovedBy,
            RemovalMethod,
            AnalystRef
        FROM staging.StoredLocalBags
        WHERE AnalystRef = @AnalystRef
    ) as src
    ON (tgt.LocationId = src.LocCode AND tgt.Session = src.SessionID AND tgt.TimeStored = TRY_CONVERT(datetime2(0), src.TimeStored))
    WHEN MATCHED THEN 
        UPDATE SET                            
            TimeStored = TRY_CONVERT(datetime2(0), src.[TimeStored]),
            TimeRemoved = TRY_CONVERT(datetime2(0), src.[TimeRemoved]),
            StoredBy = src.StoredBy,
            RemovedBy = src.RemovedBy,
            RemovalMethod = src.[RemovalMethod]
    WHEN NOT MATCHED THEN
        INSERT ([LocationId],[Session],[TimeStored],[TimeRemoved],[StoredBy],[RemovedBy],[RemovalMethod],[DeletedAt])
        VALUES (
            src.LocCode,                      
            src.[SessionID],
            TRY_CONVERT(datetime2(0), src.[TimeStored]),
            TRY_CONVERT(datetime2(0), src.[TimeRemoved]),
            src.StoredBy,
            src.RemovedBy,
            src.[RemovalMethod],
            NULL
        );                         
END;

额外优化：使用TRY_CONVERT避免转换失败

无论哪种方案，都建议用TRY_CONVERT(datetime2(0), 字符串)替代隐式转换，这样即使存在格式异常的字符串，也会返回NULL而不是直接抛出错误，便于后续排查异常数据。

内容的提问来源于stack exchange，提问作者Colin-G-Davidson

仅在MERGE语句中出现日期时间转换失败问题求助

表结构 #

Staging表结构 #

Fact表结构 #

存储过程核心代码 #

数据示例 #

转换前的日期数据 #

报错后staging表的转换后数据 #

问题原因 #

解决办法 #

方案1：使用临时表存储转换后的数据 #

方案2：在MERGE中嵌套转换逻辑，显式控制转换顺序 #

额外优化：使用TRY_CONVERT避免转换失败 #

表结构