主页 / dba / 问题 / 246600
Accepted
NicolasGutierrezToD
Asked: 2019-08-30 17:33:00 +0800 CST

操作数类型冲突：int 与 datetime2 不兼容

  • 772

下面的代码会生成此错误：

错误

消息 206，级别 16，状态 2，过程 usp_TRAC_PSE_Step_12_Resource_Request_OBJECT-Production-Part1，第 93 行操作数类型冲突：int 与 datetime2 不兼容

代码
insert into [dbo].[pse__Resource_Request__c_LOAD]
    (OwnerId, pse__End_Date__c, pse__Milestone__c, pse__Percent_Allocated__c, pse__Planned_Bill_Rate__c,
     pse__Practice__c, pse__Project__c, pse__Region__c, pse__SOW_Hours__c, pse__Request_Priority__c,
     pse__Resource_Request_Name__c, pse__Resource_Role__c, pse__Start_Date__c, pse__Status__c,
     Partner_Requested__c, OA_Migration_ID__c, CurrencyIsoCode, Percent_Onsite__c)
    
    select (select [OwnerId] from [dbo].[pse__Proj__c_LOAD] where [OA_Migration_ID__c] = p.id) 'OwnerId'
          , case when case when p.custom_35 is null then ISNULL(b.startdate, '01/01/1900 23:59:59.999') + 180 else p.custom_35 end  <  ISNULL(b.startdate, '01/01/1900 23:59:59.999')  then  ISNULL(b.startdate, '01/01/1900 23:59:59.999') + 180 else case when p.custom_35 is null then ISNULL(b.startdate, '01/01/1900 23:59:59.999') + 180 else p.custom_35 end end 'pse__End_Date__c'
          , null 'pse__Milestone__c' -- Need to figure out how to link to a milestone
          , null 'pse__Percent_Allocated__c'  -- this data is not a percentage.... either remove or data needs to be fixed
          , null 'pse__Planned_Bill_Rate__c' -- Determine if we are loading anything here
          , 'a94300000008OQEAA2'  'pse__Practice__c'  --Not sure where i can get this... talk to xxxx
          , (select [id] from [dbo].[pse__Proj__c_LOAD] where [OA_Migration_ID__c] = p.id) 'pse__Project__c'
          , (select [pse__Region__c] from [dbo].[pse__Proj__c_LOAD] where [OA_Migration_ID__c] = p.id) 'pse__Region__c'
          , b.hours 'pse__SOW_Hours__c'
          , case when p.project_stage_id = 8 then 'High' else 'Normal' End  'pse__Request_Priority__c' -- this column does not exist in the booking table as noted in the mapping document b.priority
          , substring(b.name,0,255) 'pse__Resource_Request_Name__c'
          , case when b.job_code_id = 54 then 'MC - Account Director'
                    when b.job_code_id = 19 then 'MC - Account Director'
                    when b.job_code_id = 23 then 'MC - Technology Architect'
                    when b.job_code_id = 56 then 'MC - Analytics Consultant'
                    when b.job_code_id = 41 then 'MC - Campaign Manager'
                    when b.job_code_id = 21 then 'MC - Centric Development'
                    when b.job_code_id = 17 then 'MC - Client Training Specialist'
                    when b.job_code_id = 50 then 'MC - Designer'
                    when b.job_code_id = 22 then 'MC - Technology Architect'
                    when b.job_code_id = 36 then 'MC - Data Architect'
                    when b.job_code_id = 37 then 'MC - Data Architect'
                    when b.job_code_id = 55 then 'MC - Deliverability Consultant'
                    when b.job_code_id = 7 then 'MC - Deployment Specialist'
                    when b.job_code_id = 15 then 'MC - Designer'
                    when b.job_code_id = 27 then 'MC - Technology Architect'
                    when b.job_code_id = 45 then 'MC - Project Manager'
                    when b.job_code_id = 42 then 'MC - Project Manager'
                    when b.job_code_id = 44 then 'MC - Project Manager'
                    when b.job_code_id = 46 then 'MC - Project Manager'
                    when b.job_code_id = 49 then 'MC - Project Manager'
                    when b.job_code_id = 43 then 'MC - Project Manager'
                    when b.job_code_id = 11 then 'MC - Deployment Specialist'
                    when b.job_code_id = 10 then 'MC - Deployment Specialist'
                    when b.job_code_id = 20 then 'MC - Technology Architect'
                    when b.job_code_id = 25 then 'MC - Technology Architect'
                    when b.job_code_id = 18 then 'MC - Project Manager'
                    when b.job_code_id = 26 then 'MC - Marketing Consultant'
                    when b.job_code_id = 9 then 'MC - Marketing Consultant'
                    when b.job_code_id = 31 then 'MC - Deployment Specialist'
                    when b.job_code_id = 16 then 'MC - Deployment Specialist'
                    when b.job_code_id = 35 then 'MC - Project Manager'
                    when b.job_code_id = 53 then 'MC - Marketing Consultant'
                    when b.job_code_id = 39 then 'MC - Production Specialist'
                    when b.job_code_id = 5 then 'MC - Program Manager'
                    when b.job_code_id = 3 then 'MC - Program Manager'
                    when b.job_code_id = 4 then 'MC - Program Manager'
                    when b.job_code_id = 2 then 'MC - Project Manager'
                    when b.job_code_id = 1 then 'MC - Project Manager'
                    when b.job_code_id = 47 then 'MC - Deployment Specialist'
                    when b.job_code_id = 14 then 'MC - Designer'
                    when b.job_code_id = 8 then 'MC - Marketing Consultant'
                    when b.job_code_id = 48 then 'MC - Project Manager'
                    when b.job_code_id = 24 then 'MC - Project Manager'
                    when b.job_code_id = 38 then 'MC - Designer'
                    when b.job_code_id = 52 then 'MC - Marketing Consultant'
                    when b.job_code_id = 40 then 'MC - Solution Architect'
                    when b.job_code_id = 51 then 'MC - Project Manager'
                    when b.job_code_id = 6 then 'MC - Technical Producer'
                    when b.job_code_id = 57 then 'MC - Technical QA Specialist'
                    when b.job_code_id = 12 then 'MC - Technology Architect'
                    when b.job_code_id = 13 then 'MC - Technology Specialist'
                        else case when b.job_code_id = 0 then case when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 54 then 'MC - Account Director'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id) = 19 then 'MC - Account Director'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 23 then 'MC - Technology Architect'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 56 then 'MC - Analytics Consultant'
                    when(select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 41 then 'MC - Campaign Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 21 then 'MC - Centric Development'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 17 then 'MC - Client Training Specialist'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 50 then 'MC - Designer'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 22 then 'MC - Technology Architect'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 36 then 'MC - Data Architect'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 37 then 'MC - Data Architect'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 55 then 'MC - Deliverability Consultant'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 7 then 'MC - Deployment Specialist'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 15 then 'MC - Designer'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 27 then 'MC - Technology Architect'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 45 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 42 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 44 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 46 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 49 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 43 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 11 then 'MC - Deployment Specialist'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 10 then 'MC - Deployment Specialist'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 20 then 'MC - Technology Architect'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 25 then 'MC - Technology Architect'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 18 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 26 then 'MC - Marketing Consultant'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 9 then 'MC - Marketing Consultant'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 31 then 'MC - Deployment Specialist'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 16 then 'MC - Deployment Specialist'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 35 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 53 then 'MC - Marketing Consultant'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 39 then 'MC - Production Specialist'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 5 then 'MC - Program Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 3 then 'MC - Program Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 4 then 'MC - Program Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 2 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 1 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 47 then 'MC - Deployment Specialist'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 14 then 'MC - Designer'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 8 then 'MC - Marketing Consultant'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 48 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 24 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 38 then 'MC - Designer'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 52 then 'MC - Marketing Consultant'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 40 then 'MC - Solution Architect'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 51 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 6 then 'MC - Technical Producer'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 57 then 'MC - Technical QA Specialist'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 12 then 'MC - Technology Architect'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 13 then 'MC - Technology Specialist' end
                         else 'Undefined' end   End 'pse__Resource_Role__c' -- xxxxx is going to send me the mapping file to translate job code to Org62 values
        --  , null 'pse__Staffer_Approval_Required__c' -- need to determine if this is needed
          , b.startdate 'pse__Start_Date__c'
          , case when p.project_stage_id = 8 then 'Ready to Staff' else 'Assigned' End  'pse__Status__c'-- this column does not exist in the booking table as noted in the mapping document b.approval_Status
          , case when custom_64 ='Partner Requested' and p.project_stage_id in (8, 14) then 'true' else 'false' end 'Partner_Requested__c'
          , b.id 'OA_Migration_ID__c'
          , (select CurrencyIsoCode from [dbo].[pse__Proj__c_LOAD] where [OA_Migration_ID__c] = p.id)  'CurrencyIsoCode'
          , 0 'Percent_Onsite__c'
    from openair_integration.dbo.booking b,
         [dbo].[Migrated_Projects] mp,
         openair_integration.dbo.project p
    where b.project_id = mp.id
    and b.project_id = p.id
    and booking_type_id = 8
    --and b.custom_64 <> 'SOW Hours'
    and isnull(b.deleted,0) <> 1
    --and mp.stage = 'Active'
sql-server datetime2

1 个回答

  1. Best Answer

    好吧，快速浏览一下您的这部分代码

    ISNULL(b.startdate, '01/01/1900 23:59:59.999') + 180

    我假设 b.startdate 被定义为 datetime2，因为能够通过使用以下示例复制您的错误：

    Declare @b_startdate datetime2 = sysdatetime()
select ISNULL(@b_startdate, '01/01/1900 23:59:59.999') + 180

    消息 206，级别 16，状态 2，行 2 操作数类型冲突：datetime2 与 int 不兼容

    如果您打算将 180 天添加到 datetime2，则需要包含类似于以下的DateAdd逻辑：

    Declare @b_startdate datetime2 = sysdatetime()
select DateAdd(Day,180,ISNULL(@b_startdate, '01/01/1900 23:59:59.999'))

    加法运算符适用于较旧的日期/时间类型，例如CAST('2019-08-30' AS DATETIME) + 1产生日期2019-08-31。有时，当将过去工作的代码（使用DATETIME和SMALLDATETIME类型）应用于较新的类型、、、DATETIME2和DATE时，有时会“意外”弹出此错误DATETIMEOFFSET。

    • 9

相关问题