-3

The code below generates this error:

ERROR

Msg 206, Level 16, State 2, Procedure usp_TRAC_PSE_Step_12_Resource_Request_OBJECT-Production-Part1, Line 93 Operand type clash: int is incompatible with datetime2

CODE
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'
0

1 Answer 1

10

Well, a quick look at this part of your code

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

I'm assuming that b.startdate is defined as datetime2, as was able to duplicate your error by using the following example:

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

Msg 206, Level 16, State 2, Line 2 Operand type clash: datetime2 is incompatible with int

If your intention is to add 180 days to a datetime2, you'll need to include DateAdd logic similar to this:

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

The addition operator is functional with older date/time types, for instance CAST('2019-08-30' AS DATETIME) + 1 yields the date 2019-08-31. Sometimes this error pops up "unexpectedly" when code that has worked in the past (with DATETIME and SMALLDATETIME types) is applied to the newer types, DATETIME2, DATE, and DATETIMEOFFSET.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.