如何解决存储过程中date与int类型不兼容错误

问题描述 #

存储过程此前正常运行数周，突然弹出如下错误：

Msg 206, Level 16, State 2, Procedure TimeBilling_Import_New, Line 23
Operand type clash: date is incompatible with int

尝试使用DATEADD(day, 0, GETDATE())修改后仍无法解决，存储过程代码如下：

USE [CTDD]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:
-- Create date: 1-28-2021
-- Description: TimeBilling Merge Stored Proc
-- =============================================
ALTER PROCEDURE [dbo].[TimeBilling_Import_New] 
AS
BEGIN
    SET NOCOUNT ON;

    MERGE INTO [CTDD].[DBO].[TimeBilling_Raw_New] AS TARGET 
    USING OPENQUERY ([XXX.XXXX.COM], 'SELECT * FROM insights.timebilling WHERE RowModifiedAt > DATEADD(day, 0, GetDate())') AS SOURCE 

    -- Update Operation -- 

    -- Comparison Operation

    ON (TARGET.TimeBillingId = SOURCE.TimeBillingId) 

    WHEN MATCHED 
        AND TARGET.TimeBillingClientId <> SOURCE.TimeBillingClientId
        OR TARGET.TimeBillingInsuranceId <> SOURCE.TimeBillingInsuranceId
        OR TARGET.TimeBillingPayorPlanId <> SOURCE.TimeBillingPayorPlanId
        OR TARGET.TimeBillingProviderId <> SOURCE.TimeBillingProviderId
        OR TARGET.TimeBillingServiceCodeId <> SOURCE.TimeBillingServiceCodeId
        OR TARGET.TimeBillingServiceFacilityId <> SOURCE.TimeBillingServiceFacilityId
        OR TARGET.TimeBillingBillingCreatedOn <> SOURCE.TimeBillingBillingCreatedOn
        OR TARGET.TimeBillingEntryDate <> SOURCE.TimeBillingEntryDate
        OR TARGET.TimeBillingServiceDate <> SOURCE.TimeBillingServiceDate
        OR TARGET.TimeBillingVoidDate <> SOURCE.TimeBillingVoidDate
        OR TARGET.TimeBillingDeletedDate <> SOURCE.TimeBillingDeletedDate
        OR TARGET.TimeBillingCreationTime <> SOURCE.TimeBillingCreationTime
        OR TARGET.TimeBillingVoidTime <> SOURCE.TimeBillingVoidTime
        OR TARGET.TimeBillingServiceUnits <> SOURCE.TimeBillingServiceUnits
        OR TARGET.TimeBillingDriveTime <> SOURCE.TimeBillingDriveTime
        OR TARGET.TimeBillingMilesDriven <> SOURCE.TimeBillingMilesDriven
        OR TARGET.TimeBillingClientMileageRate <> SOURCE.TimeBillingClientMileageRate
        OR TARGET.TimeBillingClientServiceRate <> SOURCE.TimeBillingClientServiceRate
        OR TARGET.TimeBillingClientAgreedServiceRate <> SOURCE.TimeBillingClientAgreedServiceRate
        OR TARGET.TimeBillingClientDriveRate <> SOURCE.TimeBillingClientDriveRate
        OR TARGET.TimeBillingProviderMileageRate <> SOURCE.TimeBillingProviderMileageRate
        OR TARGET.TimeBillingProviderDriveRate <> SOURCE.TimeBillingProviderDriveRate
        OR TARGET.TimeBillingProviderServiceRate <> SOURCE.TimeBillingProviderServiceRate
        OR TARGET.TimeBillingPatientResponsibilityAmount <> SOURCE.TimeBillingPatientResponsibilityAmount
        OR TARGET.ClientChargesTotal <> SOURCE.ClientChargesTotal
        OR TARGET.ClientChargesAgreedTotal <> SOURCE.ClientChargesAgreedTotal
        OR TARGET.TimeBillingFirstBilledDate <> SOURCE.TimeBillingFirstBilledDate
        OR TARGET.TimeBillingAuthorizationId <> SOURCE.TimeBillingAuthorizationId
        OR TARGET.TimeBillingModifierCode1 <> SOURCE.TimeBillingModifierCode1
        OR TARGET.TimeBillingModifierCode2 <> SOURCE.TimeBillingModifierCode2
        OR TARGET.TimeBillingModifierCode3 <> SOURCE.TimeBillingModifierCode3
        OR TARGET.TimeBillingModifierCode4 <> SOURCE.TimeBillingModifierCode4
        OR TARGET.TimeBillingModifierDescription1 <> SOURCE.TimeBillingModifierDescription1
        OR TARGET.TimeBillingModifierDescription2 <> SOURCE.TimeBillingModifierDescription2
        OR TARGET.TimeBillingModifierDescription3 <> SOURCE.TimeBillingModifierDescription3
        OR TARGET.TimeBillingModifierDescription4 <> SOURCE.TimeBillingModifierDescription4
        OR TARGET.TimeBillingCreateDate <> SOURCE.TimeBillingCreateDate
        OR TARGET.TimeBillingModifyDate <> SOURCE.TimeBillingModifyDate
        OR TARGET.TimeBillingServiceLocationName <> SOURCE.TimeBillingServiceLocationName
        OR TARGET.TimeBillingServiceAddress1 <> SOURCE.TimeBillingServiceAddress1
        OR TARGET.TimeBillingServiceAddress2 <> SOURCE.TimeBillingServiceAddress2
        OR TARGET.TimeBillingServiceCity <> SOURCE.TimeBillingServiceCity
        OR TARGET.TimeBillingServiceStateProvince <> SOURCE.TimeBillingServiceStateProvince
        OR TARGET.TimeBillingServicePostalCode <> SOURCE.TimeBillingServicePostalCode
        OR TARGET.TimeBillingServiceCounty <> SOURCE.TimeBillingServiceCounty
        OR TARGET.TimeBillingCreatedByContactId <> SOURCE.TimeBillingCreatedByContactId
        OR TARGET.TimeBillingModifiedByContactId <> SOURCE.TimeBillingModifiedByContactId
        OR TARGET.TimeBillingLastBilledDate <> SOURCE.TimeBillingLastBilledDate
        OR TARGET.TimeBillingGroupId <> SOURCE.TimeBillingGroupId
        OR TARGET.TimeBillingProviderSignature <> SOURCE.TimeBillingProviderSignature
        OR TARGET.TimeBillingClientSignature <> SOURCE.TimeBillingClientSignature
        OR TARGET.TimeBillingOrganizationId <> SOURCE.TimeBillingOrganizationId

    -- Update Target records --
    THEN UPDATE
        SET TARGET.TimeBillingClientId = SOURCE.TimeBillingClientId,
            TARGET.TimeBillingInsuranceId =  SOURCE.TimeBillingInsuranceId,
            TARGET.TimeBillingPayorPlanId = SOURCE.TimeBillingPayorPlanId,
            TARGET.TimeBillingProviderId = SOURCE.TimeBillingProviderId,
            TARGET.TimeBillingServiceCodeId = SOURCE.TimeBillingServiceCodeId,
            TARGET.TimeBillingServiceFacilityId = SOURCE.TimeBillingServiceFacilityId,
            TARGET.TimeBillingBillingCreatedOn = SOURCE.TimeBillingBillingCreatedOn,
            TARGET.TimeBillingEntryDate = SOURCE.TimeBillingEntryDate,
            TARGET.TimeBillingServiceDate = SOURCE.TimeBillingServiceDate,
            TARGET.TimeBillingVoidDate = SOURCE.TimeBillingVoidDate,
            TARGET.TimeBillingDeletedDate = SOURCE.TimeBillingDeletedDate,
            TARGET.TimeBillingCreationTime = SOURCE.TimeBillingCreationTime,
            TARGET.TimeBillingServiceStartTime = SOURCE.TimeBillingServiceStartTime,
            TARGET.TimeBillingServiceEndTime = SOURCE.TimeBillingServiceEndTime,
            TARGET.TimeBillingVoidTime = SOURCE.TimeBillingVoidTime,
            TARGET.TimeBillingServiceUnits = SOURCE.TimeBillingServiceUnits,
            TARGET.TimeBillingDriveTime = SOURCE.TimeBillingDriveTime,
            TARGET.TimeBillingMilesDriven = SOURCE.TimeBillingMilesDriven,
            TARGET.TimeBillingClientMileageRate = SOURCE.TimeBillingClientMileageRate,
            TARGET.TimeBillingClientServiceRate = SOURCE.TimeBillingClientServiceRate,
            TARGET.TimeBillingClientAgreedServiceRate = SOURCE.TimeBillingClientAgreedServiceRate,
            TARGET.TimeBillingClientDriveRate = SOURCE.TimeBillingClientDriveRate,
            TARGET.TimeBillingProviderMileageRate = SOURCE.TimeBillingProviderMileageRate,
            TARGET.TimeBillingProviderDriveRate = SOURCE.TimeBillingProviderDriveRate,
            TARGET.TimeBillingProviderServiceRate = SOURCE.TimeBillingProviderServiceRate,
            TARGET.TimeBillingPatientResponsibilityAmount = SOURCE.TimeBillingPatientResponsibilityAmount,
            TARGET.ClientChargesTotal = SOURCE.ClientChargesTotal,
            TARGET.ClientChargesAgreedTotal = SOURCE.ClientChargesAgreedTotal,
            TARGET.TimeBillingFirstBilledDate = SOURCE.TimeBillingFirstBilledDate,
            TARGET.TimeBillingAuthorizationId = SOURCE.TimeBillingAuthorizationId,
            TARGET.TimeBillingModifierCode1 = SOURCE.TimeBillingModifierCode1,
            TARGET.TimeBillingModifierCode2 = SOURCE.TimeBillingModifierCode2,
            TARGET.TimeBillingModifierCode3 = SOURCE.TimeBillingModifierCode3,
            TARGET.TimeBillingModifierCode4 = SOURCE.TimeBillingModifierCode4,
            TARGET.TimeBillingModifierDescription1 = SOURCE.TimeBillingModifierDescription1,
            TARGET.TimeBillingModifierDescription2 = SOURCE.TimeBillingModifierDescription2,
            TARGET.TimeBillingModifierDescription3 = SOURCE.TimeBillingModifierDescription3,
            TARGET.TimeBillingModifierDescription4 = SOURCE.TimeBillingModifierDescription4,
            TARGET.TimeBillingCreateDate = SOURCE.TimeBillingCreateDate,
            TARGET.TimeBillingModifyDate = SOURCE.TimeBillingModifyDate,
            TARGET.TimeBillingServiceLocationName = SOURCE.TimeBillingServiceLocationName,
            TARGET.TimeBillingServiceAddress1 = SOURCE.TimeBillingServiceAddress1,
            TARGET.TimeBillingServiceAddress2 = SOURCE.TimeBillingServiceAddress2,
            TARGET.TimeBillingServiceCity = SOURCE.TimeBillingServiceCity,
            TARGET.TimeBillingServiceStateProvince = SOURCE.TimeBillingServiceStateProvince,
            TARGET.TimeBillingServicePostalCode = SOURCE.TimeBillingServicePostalCode,
            TARGET.TimeBillingServiceCounty = SOURCE.TimeBillingServiceCounty,
            TARGET.TimeBillingCreatedByContactId = SOURCE.TimeBillingCreatedByContactId,
            TARGET.TimeBillingModifiedByContactId = SOURCE.TimeBillingModifiedByContactId,
            TARGET.TimeBillingLastBilledDate = SOURCE.TimeBillingLastBilledDate,
            TARGET.TimeBillingGroupId = SOURCE.TimeBillingGroupId,
            TARGET.TimeBillingProviderSignature = SOURCE.TimeBillingProviderSignature,
            TARGET.TimeBillingClientSignature = SOURCE.TimeBillingClientSignature,
            TARGET.TimeBillingOrganizationId = SOURCE.TimeBillingOrganizationId

    -- Insert Operation --

    -- Insert Unmatched Records into Target -- 
    WHEN NOT MATCHED BY TARGET 
    THEN INSERT (TimeBillingClientId,
        TimeBillingInsuranceId,
        TimeBillingPayorPlanId,
        TimeBillingProviderId,
        TimeBillingServiceCodeId,
        TimeBillingServiceFacilityId,
        TimeBillingBillingCreatedOn,
        TimeBillingEntryDate,
        TimeBillingServiceDate,
        TimeBillingVoidDate,
        TimeBillingDeletedDate,
        TimeBillingCreationTime,
        TimeBillingServiceStartTime,
        TimeBillingServiceEndTime,
        TimeBillingVoidTime,
        TimeBillingId,
        TimeBillingServiceUnits,
        TimeBillingDriveTime,
        TimeBillingMilesDriven,
        TimeBillingClientMileageRate,
        TimeBillingClientServiceRate,
        TimeBillingClientAgreedServiceRate,
        TimeBillingClientDriveRate,
        TimeBillingProviderMileageRate,
        TimeBillingProviderDriveRate,
        TimeBillingProviderServiceRate,
        TimeBillingPatientResponsibilityAmount,
        ClientChargesTotal,
        ClientChargesAgreedTotal,
        TimeBillingFirstBilledDate,
        TimeBillingAuthorizationId,
        TimeBillingModifierCode1,
        TimeBillingModifierCode2,
        TimeBillingModifierCode3,
        TimeBillingModifierCode4,
        TimeBillingModifierDescription1,
        TimeBillingModifierDescription2,
        TimeBillingModifierDescription3,
        TimeBillingModifierDescription4,
        TimeBillingCreateDate,
        TimeBillingModifyDate,
        TimeBillingServiceLocationName,
        TimeBillingServiceAddress1,
        TimeBillingServiceAddress2,
        TimeBillingServiceCity,
        TimeBillingServiceStateProvince,
        TimeBillingServicePostalCode,
        TimeBillingServiceCounty,
        TimeBillingCreatedByContactId,
        TimeBillingModifiedByContactId,
        TimeBillingLastBilledDate,
        TimeBillingGroupId,
        TimeBillingProviderSignature,
        TimeBillingClientSignature,
        TimeBillingOrganizationId)       
        VALUES (SOURCE.TimeBillingClientId,
        SOURCE.TimeBillingInsuranceId,
        SOURCE.TimeBillingPayorPlanId,
        SOURCE.TimeBillingProviderId,
        SOURCE.TimeBillingServiceCodeId,
        SOURCE.TimeBillingServiceFacilityId,
        SOURCE.TimeBillingBillingCreatedOn,
        SOURCE.TimeBillingEntryDate,
        SOURCE.TimeBillingServiceDate,
        SOURCE.TimeBillingVoidDate,
        SOURCE.TimeBillingDeletedDate,
        SOURCE.TimeBillingCreationTime,
        SOURCE.TimeBillingServiceStartTime,
        SOURCE.TimeBillingServiceEndTime,
        SOURCE.TimeBillingVoidTime,
        SOURCE.TimeBillingId,
        SOURCE.TimeBillingServiceUnits,
        SOURCE.TimeBillingDriveTime,
        SOURCE.TimeBillingMilesDriven,
        SOURCE.TimeBillingClientMileageRate,
        SOURCE.TimeBillingClientServiceRate,
        SOURCE.TimeBillingClientAgreedServiceRate,
        SOURCE.TimeBillingClientDriveRate,
        SOURCE.TimeBillingProviderMileageRate,
        SOURCE.TimeBillingProviderDriveRate,
        SOURCE.TimeBillingProviderServiceRate,
        SOURCE.TimeBillingPatientResponsibilityAmount,
        SOURCE.ClientChargesTotal,
        SOURCE.ClientChargesAgreedTotal,
        SOURCE.TimeBillingFirstBilledDate,
        SOURCE.TimeBillingAuthorizationId,
        SOURCE.TimeBillingModifierCode1,
        SOURCE.TimeBillingModifierCode2,
        SOURCE.TimeBillingModifierCode3,
        SOURCE.TimeBillingModifierCode4,
        SOURCE.TimeBillingModifierDescription1,
        SOURCE.TimeBillingModifierDescription2,
        SOURCE.TimeBillingModifierDescription3,
        SOURCE.TimeBillingModifierDescription4,
        SOURCE.TimeBillingCreateDate,
        SOURCE.TimeBillingModifyDate,
        SOURCE.TimeBillingServiceLocationName,
        SOURCE.TimeBillingServiceAddress1,
        SOURCE.TimeBillingServiceAddress2,
        SOURCE.TimeBillingServiceCity,
        SOURCE.TimeBillingServiceStateProvince,
        SOURCE.TimeBillingServicePostalCode,
        SOURCE.TimeBillingServiceCounty,
        SOURCE.TimeBillingCreatedByContactId,
        SOURCE.TimeBillingModifiedByContactId,
        SOURCE.TimeBillingLastBilledDate,
        SOURCE.TimeBillingGroupId,
        SOURCE.TimeBillingProviderSignature,
        SOURCE.TimeBillingClientSignature,
        SOURCE.TimeBillingOrganizationId) 

    -- Delete Operation --

    -- Delete Records in Target that do not exist in source --

;
-- end of merge operation --
END

错误原因分析 #

错误根源在OPENQUERY的远程查询语句中：SELECT * FROM insights.timebilling WHERE RowModifiedAt > DATEADD(day, 0, GetDate())。

• 远程服务器（XXX.XXXX.COM）执行该语句时，DATEADD返回的类型与RowModifiedAt字段类型不匹配；
• 可能是远程表RowModifiedAt字段类型被修改（比如从datetime改为int，或反之）；
• DATEADD(day, 0, GETDATE())属于冗余写法，直接用GETDATE()即可，但无法解决类型冲突核心问题。

解决方法 #

方法1：本地生成日期字符串传递给远程查询 #

避免在远程服务器执行日期函数，本地生成标准格式的日期字符串后传入：

DECLARE @LocalDate NVARCHAR(20) = CONVERT(NVARCHAR(20), GETDATE(), 120)
MERGE INTO [CTDD].[DBO].[TimeBilling_Raw_New] AS TARGET 
USING OPENQUERY ([XXX.XXXX.COM], 'SELECT * FROM insights.timebilling WHERE RowModifiedAt > ''' + @LocalDate + '''') AS SOURCE 

注：可根据远程数据库的日期格式调整转换样式（比如121对应带毫秒的ISO8601格式）。

方法2：明确指定远程查询的日期类型 #

如果远程数据库是SQL Server，强制将GETDATE()转换为与`RowModifiedAt