DateTime 转换仅在 MERGE 语句中失败-解网

问：

我正在运行一个过程，该过程转换临时表中的某些数据，然后将其合并到事实表中。其中 2 列是 varchar，因为数据提供程序将导出时的日期时间字段格式化为英语可读（20/09/2023 16：48：00）。在此过程中，系统返回错误“从字符串转换日期和/或时间时转换失败”。我不确定为什么会发生这种情况，因为当我在失败后检查种子表时，所有值都是 NULL 或格式正确。我还可以运行一个 SELECT 查询，在其中执行相同的转换，结果显示良好，没有任何转换错误，因此我必须假设问题出在我的 PROC 中。

这是临时表架构

[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;

2 个日期列以前看起来像这样。

时间存储	时间已删除
19/09/2023 16:50:00	20/09/2023 12:05:00
19/09/2023 17:25:00	零
15/09/2023 09:15:00	18/09/2023 14:30:00

转换后，一旦显示错误，如果我检查表格，它就会显示

时间存储	时间已删除
2023-09-19 16:50:00	2023-09-20 12:05:00
2023-09-19 17:25:00	零
2023-09-15 09:15:00	2023-09-18 14:30:00

因此，如您所见，转换适用于所有非 NULL 值。不确定它是否相关，但只是补充一点，我无法控制暂存数据的交付方式，我必须按当前状态获取它并在我的服务器上进行转换。

sql-server 日期时间

您应该移动 src。AnalystRef = 合并联接@AnalystRef事物。另外，在茫茫人海中随机的 BEGIN / END 是怎么回事。最后，很难猜测，但它可能会提前对行进行上转换，因此不属于同一 analystRef 的其他行仍在转换中。或者您使用的日期格式不好，因为它取决于系统设置。set dateformat 'dmy' select cast('2023-09-19 16:50:00' as datetime)

0赞 Panagiotis Kanavos 9/21/2023

转换可能由于多种原因而失败，而不仅仅是在 MERGE 中。在 WHERE 或 JOIN 中，或者任何尝试使用服务器的本地化设置将未知字符串与日期进行比较时，它们将因相同的原因而失败。请改用正确的类型。

答：

0赞 Colin-G-Davidson 10/4/2023 #1

它似乎可以通过上述 2 条评论的组合来解决。日期转换似乎发生在过滤器之前，因此这导致了错误。

在此用例中，切换到带有样式 103 的推荐 CONVERT 函数，而不是使用子字符串构建它确实有所帮助。

请注意，这只在从英国格式转换为 ISO 8601 的用例中有所帮助，而在另一个用例中没有帮助，因此我将生成一个新问题来涵盖该特定问题。

上一个：sp_get_query_template不适用于 ODBC 时间戳

下一个：日期时间与日期字符串的 Hasura 共换

DateTime 转换仅在 MERGE 语句中失败

DateTime conversion fails only in MERGE statement

评论