Clarity

 View Only
  • 1.  Synchronize portfolio investments job error - the datediff function resulted in an overflow

    Posted May 20, 2022 07:43 PM
    Hi all,

    We are trying to sync Portfolios in CA PPM (v15.8.0) using Synchronize portfolio investments job, but jobs are failing with error.

    ERROR 2022-05-20 18:28:28,358 [Dispatch Synchronize portfolio investments : bg@S1PPMP01 (tenant=clarity)] job.PortfolioSyncJob (clarity:admin:95654600__B4AEA4D6-058C-42ED-9DC6-27F9DA39D615:Synchronize portfolio investments) ---> could not execute (5325010)
    com.niku.union.persistence.PersistenceException:
    SQL error code: 535
    Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

    select odf_q.* , (select top 1 investment_type_name from ( SELECT o.code AS investment_type_code,
    c.name AS investment_type_name,
    c.last_updated_date AS last_updated_date,
    lang.id AS language_id,
    lang.language_code AS language_code
    FROM odf_objects o,
    cmn_captions_nls c,
    odf_object_extensions oe,
    cmn_languages lang
    WHERE c.pk_id = o.ID
    AND 1=? and 1=1 and 1=1 AND c.table_name = 'ODF_OBJECTS'
    AND c.language_code = ?
    AND oe.extension_code = 'inv'
    AND lang.language_code = c.language_code
    AND lang.language_code = ?
    AND o.is_customizable = 1
    AND o.is_custom = 0
    AND oe.object_code = o.code) q_investment_type where q_investment_type.investment_type_code = odf_q.investment_type) investment_type_caption , (select top 1 NAME from cmn_lookups_v where language_code = ? and lookup_type = ? and LOOKUP_CODE = odf_q.stage_code) as stage_code_caption , (select top 1 NAME from cmn_lookups_v where language_code = ? and lookup_type = ? and LOOKUP_ENUM = odf_q.obj_align_factor2) as obj_align_factor2_caption , (select top 1 NAME from cmn_lookups_v where language_code = ? and lookup_type = ? and LOOKUP_ENUM = odf_q.obj_align_factor1) as obj_align_factor1_caption , (select top 1 NAME from cmn_lookups_v where language_code = ? and lookup_type = ? and LOOKUP_ENUM = odf_q.obj_align_factor4) as obj_align_factor4_caption , (select top 1 NAME from cmn_lookups_v where language_code = ? and lookup_type = ? and LOOKUP_ENUM = odf_q.obj_align_factor3) as obj_align_factor3_caption , (select top 1 NAME from cmn_lookups_v where language_code = ? and lookup_type = ? and LOOKUP_ENUM = odf_q.obj_align_factor6) as obj_align_factor6_caption , (select top 1 NAME from cmn_lookups_v where language_code = ? and lookup_type = ? and LOOKUP_ENUM = odf_q.obj_align_factor5) as obj_align_factor5_caption , (select top 1 lookup_name from ( SELECT DISTINCT
    LOOKUP.LOOKUP_CODE AS LOOKUP_CODE,
    LOOKUP.NAME AS LOOKUP_NAME,
    LOOKUP.SORT_ORDER AS LOOKUP_SORT,
    LOOKUP.LAST_UPDATED_DATE AS LAST_UPDATED_DATE,
    LANG.LANGUAGE_CODE AS LANGUAGE_CODE,
    LANG.ID AS LANGUAGE_ID
    FROM ODF_CA_COP_IDEA_PRJ_MAP MAP, CMN_LOOKUPS_V LOOKUP, CMN_LANGUAGES LANG
    WHERE MAP.CHILD_LOOKUP = LOOKUP.LOOKUP_CODE
    AND LOOKUP.LANGUAGE_CODE = LANG.LANGUAGE_CODE
    AND LOOKUP.LOOKUP_TYPE = 'OBJ_IDEA_PROJECT_CATEGORY'
    AND LOOKUP.LANGUAGE_CODE = ?
    AND (? IS NULL OR
    ? = MAP.PARENT_LOOKUP)
    AND 1=? and 1=1 and 1=1) q_obj_request_category where q_obj_request_category.lookup_code = odf_q.obj_request_category) obj_request_category_caption , (select top 1 NAME from cmn_lookups_v where language_code = ? and lookup_type = ? and LOOKUP_CODE = odf_q.goal_code) as goal_code_caption , (select top 1 NAME from cmn_lookups_v where language_code = ? and lookup_type = ? and LOOKUP_ENUM = odf_q.bnbti_urgencia) as bnbti_urgencia_caption , (select top 1 NAME from cmn_lookups_v where language_code = ? and lookup_type = ? and LOOKUP_CODE = odf_q.obj_pfl_category1) as obj_pfl_category1_caption , (select top 1 NAME from cmn_lookups_v where language_code = ? and lookup_type = ? and LOOKUP_CODE = odf_q.obj_pfl_category2) as obj_pfl_category2_caption , (select top 1 NAME from cmn_lookups_v where language_code = ? and lookup_type = ? and LOOKUP_CODE = odf_q.obj_pfl_category3) as obj_pfl_category3_caption , (select top 1 NAME from cmn_lookups_v where language_code = ? and lookup_type = ? and LOOKUP_CODE = odf_q.obj_pfl_category4) as obj_pfl_category4_caption , (select top 1 NAME from cmn_lookups_v where language_code = ? and lookup_type = ? and LOOKUP_ENUM = odf_q.status) as status_caption , (select top 1 NAME from cmn_lookups_v where language_code = ? and lookup_type = ? and LOOKUP_ENUM = odf_q.bnbti_gravidade_proj) as bnbti_gravidade_proj_caption , (select top 1 prname from ( SELECT prID AS prID,
    PRUID AS UNIQUE_CODE,
    prName AS prName,
    prExternalID AS prExternalID,
    prModTime AS LAST_UPDATED_DATE
    FROM PRChargeCode
    WHERE 1=? and 1=1 and 1=1) q_chargecodeid where q_chargecodeid.prid = odf_q.chargecodeid) chargecodeid_caption , (select top 1 NAME from cmn_lookups_v where language_code = ? and lookup_type = ? and LOOKUP_ENUM = odf_q.bnbti_tendencia) as bnbti_tendencia_caption , (select top 1 NAME from cmn_lookups_v where language_code = ? and lookup_type = ? and LOOKUP_ENUM = odf_q.status_indicator) as status_indicator_caption , (select top 1 full_name from ( SELECT u.id AS user_id,
    u.user_name AS user_name,
    u.user_name AS UNIQUE_CODE,
    r.id AS resource_id,
    r.unique_name AS unique_name,
    r.first_name AS first_name,
    r.last_name AS last_name,
    r.full_name AS full_name,
    r.unique_name AS resourceID,
    r.person_type AS person_type_id,
    l.name AS person_type,
    u.user_status_id AS user_status_id,
    s.name AS user_status
    FROM srm_resources r,
    cmn_sec_users u,
    cmn_lookups_v l,
    cmn_lookups_v s
    WHERE u.id = r.user_id
    AND 1=? and 1=1 and 1=1 AND case when r.user_id is null then 0 else r.user_id end != -99
    AND r.person_type = l.id
    AND l.language_code=?
    AND l.lookup_type='SRM_RESOURCE_TYPE'
    AND u.user_status_id = s.id
    AND u.IS_HIDDEN = 0
    AND s.language_code= ?
    AND s.lookup_type='SEC_USER_STATUS'

    AND 1=1) q_manager_id where q_manager_id.user_id = odf_q.manager_id) manager_id_caption , (select top 1 NAME from cmn_lookups_v where language_code = ? and lookup_type = ? and LOOKUP_CODE = odf_q.bnbti_mandatorio) as bnbti_mandatorio_caption , (select top 1 currency_name from ( SELECT c.currency_code AS currency_code,
    c.currency_code AS currency_name,
    c.LAST_UPDATED_DATE AS LAST_UPDATED_DATE
    FROM cmn_currencies c
    WHERE c.is_active = 1
    AND 1=? and 1=1 and 1=1) q_currency_code where q_currency_code.currency_code = odf_q.currency_code) currency_code_caption , (select top 1 NAME from cmn_lookups_v where language_code = ? and lookup_type = ? and LOOKUP_CODE = odf_q.obj_request_type) as obj_request_type_caption , (select top 1 name from ( select o.code AS code,
    nls.name AS name,
    nls.description AS description,o.LAST_UPDATED_DATE AS LAST_UPDATED_DATE
    ,l.ID AS LANGUAGE_ID,
    nls.LANGUAGE_CODE AS LANGUAGE_CODE
    from odf_objects o, cmn_captions_nls nls,CMN_LANGUAGES l
    where o.id = nls.pk_id
    and nls.language_code = ?
    and nls.table_name = 'ODF_OBJECTS'
    AND nls.LANGUAGE_CODE = l.LANGUAGE_CODE
    and 1=? and 1=1 and 1=1) q_odf_object_code where q_odf_object_code.code = odf_q.odf_object_code) odf_object_code_caption , (select top 1 NAME from cmn_lookups_v where language_code = ? and lookup_type = ? and LOOKUP_CODE = odf_q.cost_type) as cost_type_caption , (select top 1 NAME from cmn_lookups_v where language_code = ? and lookup_type = ? and LOOKUP_CODE = odf_q.obj_work_status) as obj_work_status_caption from ( select row_number() over ( order by odf_pk) odf_row_num, count(*) over (partition by pmd_analytical_partition_by) odf_num_rows ,odf_cols.* from ( select 'x' pmd_analytical_partition_by, inv_investments.id odf_pk , inv_investments.code COLLATE Latin1_General_CI_AS_KS as unique_code , EVREC.ITD_BCWS as ev_bcws , EVREC.ITD_BCWP as ev_bcwp , ODF_CA_INV.odf_object_code COLLATE Latin1_General_CI_AS_KS as investment_type , inv_investments.STAGE_CODE COLLATE Latin1_General_CI_AS_KS as stage_code , odf_ca_inv.obj_align_factor2 as obj_align_factor2 , odf_ca_inv.obj_align_factor1 as obj_align_factor1 , odf_ca_inv.obj_align_factor4 as obj_align_factor4 , odf_ca_inv.obj_align_factor3 as obj_align_factor3 , odf_ca_inv.obj_align_factor6 as obj_align_factor6 , odf_ca_inv.obj_align_factor5 as obj_align_factor5 , (ISNULL(INV_INVESTMENTS.labor_eacsum,0)/3600)/(select hours_per_day from prj_hours_per_day_v) as ttl_effort , odf_ca_project.obj_request_category COLLATE Latin1_General_CI_AS_KS as obj_request_category , (case when inv_investments.schedule_start is not null and inv_investments.schedule_finish is not null THEN
    (ISNULL(INV_INVESTMENTS.LABOR_EACSUM,0) / 3600 / niku.PRJ_HPD_FACTOR_FCT())
    ELSE 0 end) as labor_eacsum , EVREC.BAC as ev_bac , inv_investments.GOAL_CODE COLLATE Latin1_General_CI_AS_KS as goal_code , inv_investments.schedule_start as schedule_start , (SELECT count(id) FROM cmn_lookups_v WHERE parent_lookup_code = INV_INVESTMENTS.process_code AND is_active = 1 AND language_code = 'en') as stage_count , (CASE WHEN (CASE WHEN (odf_ca_inv.obj_align_factor1*1) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (odf_ca_inv.obj_align_factor2*1) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (odf_ca_inv.obj_align_factor3*1) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (odf_ca_inv.obj_align_factor4*1) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (odf_ca_inv.obj_align_factor5*1) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (odf_ca_inv.obj_align_factor6*1) IS NOT NULL THEN 1 ELSE 0 END)=0 THEN NULL ELSE (ISNULL((odf_ca_inv.obj_align_factor1*1),0)+ISNULL((odf_ca_inv.obj_align_factor2*1),0)+ISNULL((odf_ca_inv.obj_align_factor3*1),0)+ISNULL((odf_ca_inv.obj_align_factor4*1),0)+ISNULL((odf_ca_inv.obj_align_factor5*1),0)+ISNULL((odf_ca_inv.obj_align_factor6*1),0))/(CASE WHEN (odf_ca_inv.obj_align_factor1*1) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (odf_ca_inv.obj_align_factor2*1) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (odf_ca_inv.obj_align_factor3*1) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (odf_ca_inv.obj_align_factor4*1) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (odf_ca_inv.obj_align_factor5*1) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (odf_ca_inv.obj_align_factor6*1) IS NOT NULL THEN 1 ELSE 0 END) END ) as obj_alignment , inv_investments.ALIGNMENT as alignment , ISNULL((SELECT count(ID) FROM cmn_lookups_v WHERE parent_lookup_code = INV_INVESTMENTS.process_code AND language_code = 'en' AND IS_ACTIVE = 1 AND SORT_ORDER <= (select sort_order from cmn_lookups_v WHERE lookup_code = INV_INVESTMENTS.stage_code AND lookup_type='INV_STAGE_TYPE' AND language_code = 'en')),0) as stage_number , ((CASE WHEN (EVREC.BAC*1.00)=0 THEN NULL ELSE (((EVREC.ETC*1.00)+(EVREC.ITD_ACWP*1.00))-(EVREC.BAC*1.00))/(EVREC.BAC*1.00) END )*(100.0*1.00)) as obj_cost_pct_var , EVREC.ITD_ACWP as ev_acwp , ((CASE WHEN (EVREC.ITD_BCWS*1.00)=0 THEN NULL ELSE ((EVREC.ITD_BCWP*1.00)-(EVREC.ITD_BCWS*1.00))/(EVREC.ITD_BCWS*1.00) END )*(100.0*1.00)) as obj_ev_sv_pct , inv_projects.percent_complete as percent_complete , (case when inv_investments.schedule_start is not null and inv_investments.schedule_finish is not null THEN
    (ISNULL(INV_INVESTMENTS.LABOR_ETCSUM,0) / 3600 / niku.PRJ_HPD_FACTOR_FCT())
    ELSE 0 end) as labor_etcsum , odf_ca_project.bnbti_urgencia as bnbti_urgencia , odf_ca_inv.obj_pfl_category1 COLLATE Latin1_General_CI_AS_KS as obj_pfl_category1 , odf_ca_inv.obj_pfl_category2 COLLATE Latin1_General_CI_AS_KS as obj_pfl_category2 , odf_ca_inv.obj_pfl_category3 COLLATE Latin1_General_CI_AS_KS as obj_pfl_category3 , odf_ca_inv.obj_pfl_category4 COLLATE Latin1_General_CI_AS_KS as obj_pfl_category4 , inv_investments.STATUS as status , inv_investments.IS_ACTIVE as is_active , (ISNULL(EVREC.ITD_BCWP,0) - ISNULL(EVREC.ITD_BCWS,0)) as ev_sv , BASEREC.FINISH_DATE as baseline_finish , ((CASE WHEN (EVREC.ITD_BCWP*1.00)=0 THEN NULL ELSE ((EVREC.ITD_BCWP*1.00)-(EVREC.ITD_ACWP*1.00))/(EVREC.ITD_BCWP*1.00) END )*(100.0*1.00)) as obj_ev_cv_pct , (ISNULL(INV_INVESTMENTS.labor_etcsum,0)/3600)/(select hours_per_day from prj_hours_per_day_v) as etc , (EVREC.ITD_ACWP + EVREC.ETC) as ev_eac_t , (CASE WHEN ISNULL(EVREC.ITD_ACWP,0) > 0
    AND ISNULL(EVREC.ITD_BCWP,0) > 0
    THEN EVREC.ITD_ACWP + ( ( (EVREC.BAC - EVREC.ITD_BCWP) * EVREC.ITD_ACWP ) / ( EVREC.ITD_BCWP ))
    ELSE NULL END) as ev_eac , (BASEREC.USAGE_SUM / 3600) / niku.PRJ_HPD_FACTOR_FCT() as baseline_usage , odf_ca_project.bnbti_gravidade_proj as bnbti_gravidade_proj , (((EVREC.ETC*1.00)+(EVREC.ITD_ACWP*1.00))-(EVREC.BAC*1.00)) as obj_cost_amt_var , inv_investments.chargecodeid as chargecodeid , INV_INVESTMENTS.CREATED_DATE as created_date , EVREC.ETC as ev_etc , (CASE WHEN ISNULL(EVREC.ITD_ACWP,0) > 0
    THEN ISNULL(EVREC.ITD_BCWP,0) / EVREC.ITD_ACWP
    ELSE NULL END) as ev_cpi , odf_ca_project.obj_objective COLLATE Latin1_General_CI_AS_KS as obj_objective , odf_ca_project.bnbti_tendencia as bnbti_tendencia , inv_investments.STATUS_INDICATOR as status_indicator , (CASE WHEN ISNULL(EVREC.ITD_BCWS,0) > 0
    THEN ISNULL(EVREC.ITD_BCWP,0) / EVREC.ITD_BCWS
    ELSE NULL END) as ev_spi , (NIKU.CMN_DATE_TIME_DIFF_FCT('Day',inv_investments.schedule_finish,BASEREC.FINISH_DATE)) as obj_schedule_var , inv_investments.MANAGER_ID as manager_id , (ISNULL(INV_INVESTMENTS.labor_actsum,0)/3600)/(select hours_per_day from prj_hours_per_day_v) as actuals , inv_investments.schedule_finish as schedule_finish , odf_ca_project.bnbti_alinhament_est as bnbti_alinhament_est , (CASE WHEN ISNULL(EVREC.BAC,0) > 0
    AND ISNULL(EVREC.ITD_BCWP,0) > 0
    AND ISNULL(EVREC.ITD_ACWP,0) > 0
    THEN ( ( EVREC.BAC - EVREC.ITD_BCWP ) * EVREC.ITD_ACWP ) / ( EVREC.ITD_BCWP )
    ELSE NULL END) as ev_etc_t , inv_investments.name COLLATE Latin1_General_CI_AS_KS as name , inv_investments.description COLLATE Latin1_General_CI_AS_KS as description , odf_ca_project.bnbti_mandatorio COLLATE Latin1_General_CI_AS_KS as bnbti_mandatorio , ((ISNULL(INV_INVESTMENTS.LABOR_ETCSUM,0) + ISNULL(INV_INVESTMENTS.LABOR_ACTSUM,0)) / 3600 / niku.PRJ_HPD_FACTOR_FCT()) as labor_effort , (inv_projects.RCF_OBJECTIVES * 1.000000 + inv_projects.RCF_SPONSORSHIP * 1.000000 + inv_projects.RCF_FUNDING * 1.000000 + inv_projects.RCF_RESOURCE_AVAIL * 1.000000 + inv_projects.RCF_INTERDEPENDENCY * 1.000000 + inv_projects.RCF_TECHNICAL * 1.000000 + inv_projects.RCF_HUMAN_INTERFACE * 1.000000 + inv_projects.RCF_ORG_CULTURE * 1.000000 + inv_projects.RCF_SUPPORTABILITY * 1.000000 + inv_projects.RCF_IMPLEMENTATION * 1.000000 + inv_projects.RCF_FLEXIBILITY * 1.000000) / (1.000000 + 1.000000 + 1.000000 + 1.000000 + 1.000000 + 1.000000 + 1.000000 + 1.000000 + 1.000000 + 1.000000 + 1.000000) as risk , ISNULL(EVREC.ITD_BCWP,0) - ISNULL(EVREC.ITD_ACWP,0) as ev_cv , (CASE WHEN ISNULL(EVREC.BAC,0) > 0 AND ISNULL(EVREC.ITD_BCWP,0) > 0
    THEN EVREC.BAC - EVREC.ITD_BCWP
    ELSE NULL END) as ev_etc_at , BASEREC.START_DATE as baseline_start , ((CASE WHEN ((BASEREC.USAGE_SUM / 3600) / niku.PRJ_HPD_FACTOR_FCT()*1.00)=0 THEN NULL ELSE (((ISNULL(INV_INVESTMENTS.labor_eacsum,0)/3600)/(select hours_per_day from prj_hours_per_day_v)*1.00)-((BASEREC.USAGE_SUM / 3600) / niku.PRJ_HPD_FACTOR_FCT()*1.00))/((BASEREC.USAGE_SUM / 3600) / niku.PRJ_HPD_FACTOR_FCT()*1.00) END )*(100.0*1.00)) as obj_effort_var , inv_investments.CURRENCY_CODE COLLATE Latin1_General_CI_AS_KS as currency_code , odf_ca_project.obj_request_type COLLATE Latin1_General_CI_AS_KS as obj_request_type , (CASE WHEN ISNULL(EVREC.BAC,0) > 0 AND ISNULL(EVREC.ITD_BCWP,0) > 0
    THEN EVREC.ITD_ACWP + (EVREC.BAC - EVREC.ITD_BCWP)
    ELSE NULL END) as ev_eac_at , odf_ca_inv.odf_object_code COLLATE Latin1_General_CI_AS_KS as odf_object_code , PAC_MNT_PROJECTS.COST_TYPE COLLATE Latin1_General_CI_AS_KS as cost_type , inv_investments.labor_eac_curve as labor_eac , odf_ca_inv.obj_work_status COLLATE Latin1_General_CI_AS_KS as obj_work_status , CASE WHEN ISNULL(INV_INVESTMENTS.labor_eacsum,0) > 0
    THEN ISNULL(INV_INVESTMENTS.labor_actsum,0)/ISNULL(INV_INVESTMENTS.labor_eacsum,0)
    ELSE 0 END as pct_complete , inv_investments.IS_REQUIRED as is_required , INV_INVESTMENTS.LAST_UPDATED_DATE as last_updated_date , (((ISNULL(INV_INVESTMENTS.labor_eacsum,0)/3600)/(select hours_per_day from prj_hours_per_day_v)*1.00)-((BASEREC.USAGE_SUM / 3600) / niku.PRJ_HPD_FACTOR_FCT()*1.00)) as obj_effort_amt_var ,inv_investments.process_code from inv_investments inv_investments LEFT OUTER JOIN prj_ev_history EVREC ON EVREC.OBJECT_ID = INV_INVESTMENTS.ID AND EVREC.OBJECT_TYPE='PROJECT' AND EVREC.PERIOD_NUMBER=0 LEFT OUTER JOIN prj_baseline_details BASEREC ON BASEREC.BASELINE_ID = INV_INVESTMENTS.BASELINE_ID AND BASEREC.OBJECT_TYPE='PROJECT', inv_projects inv_projects, srm_resources CREATED, srm_resources UPDATED, odf_ca_inv odf_ca_inv, fin_financials fin_financials, odf_object_instance_mapping oim7, odf_ca_financials odf_ca_financials, pac_mnt_projects PAC_MNT_PROJECTS, odf_ca_projfinproperties odf_ca_projfinproperties, odf_ca_project odf_ca_project , INV_INVESTMENTS PFMPI_INV LEFT OUTER JOIN PFM_PINNED_INVESTMENTS PFMPI_PI ON PFMPI_INV.ID = PFMPI_PI.INVESTMENT_ID AND PFMPI_PI.PORTFOLIO_ID = 5000001 where PFMPI_INV.ID = INV_INVESTMENTS.ID AND INV_INVESTMENTS.IS_CUSTOM = 0 AND (1=1 OR PFMPI_PI.ID IS NOT NULL) AND INV_PROJECTS.IS_TEMPLATE = 0 and inv_investments.id = inv_projects.prid and inv_investments.created_by = CREATED.user_id and inv_investments.last_updated_by = UPDATED.user_id and inv_investments.id = odf_ca_inv.id and inv_investments.id = oim7.primary_object_instance_id and oim7.primary_object_instance_code = 'project' and fin_financials.id = oim7.secondary_object_instance_id and oim7.secondary_object_instance_code = 'financials' and FIN_FINANCIALS.id = odf_ca_financials.id and inv_investments.id = PAC_MNT_PROJECTS.id and inv_investments.id = odf_ca_projfinproperties.id and inv_investments.id = odf_ca_project.id and odf_ca_inv.odf_object_code = ? and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 ) odf_cols ) odf_q where odf_q.odf_row_num between ? and ? order by odf_q.odf_row_num

    Does anyone seen this issue before?

    Thanks a lot,
    Chris


  • 2.  RE: Synchronize portfolio investments job error - the datediff function resulted in an overflow

    Broadcom Employee
    Posted May 23, 2022 08:43 AM
    Chris,
    Please check this article:

    SQL Server datediff function overflow when date/time instances is too large

    There was an issue with this when the date range is too large between the Start and Finish

    ------------------------------
    Liz Williamson
    Principal Support Engineer - Clarity
    Broadcom
    ------------------------------



  • 3.  RE: Synchronize portfolio investments job error - the datediff function resulted in an overflow

    Posted May 24, 2022 08:49 AM
    Hi Liz!

    I read the article and that was exactly the issue.

    However, I don't know why, we are not able to change the end date of the tasks.

    Do you know how to go about solving this?


    Thanks a lot,
    Chris


  • 4.  RE: Synchronize portfolio investments job error - the datediff function resulted in an overflow

    Broadcom Employee
    Posted May 24, 2022 08:57 AM
    Hi Chris,

    First look at the assignments on the tasks and see if you can change it at the assignment level.
    Either on the assignment or the task if there is no ETC you might need to some in order to change the finish date.

    If you need assistance please open a case with support.  You can mention me in it as I know the issue already.

    ------------------------------
    Liz Williamson
    Principal Support Engineer - Clarity
    Broadcom
    ------------------------------



  • 5.  RE: Synchronize portfolio investments job error - the datediff function resulted in an overflow

    Posted May 24, 2022 09:43 AM
    Hi Liz,

    Thanks, but our version (15.8.0) is no longer supported. The last cases could not be dealt with. We are planning to upgrade, but we don't have a plan in place yet.

    I looked at the tasks and they all already have assignments, some with ETC and some without ETC. Just a few below, as an example.


    Thanks,
    Chris


  • 6.  RE: Synchronize portfolio investments job error - the datediff function resulted in an overflow

    Posted May 25, 2022 10:27 AM
    Hi Liz!

    We were able to change assignments and correct the year of tasks.

    However, just a project that we still haven't managed, but we didn't find tasks with a very future year or assignments like that.

    Do you have any idea what it could be?

    Thanks,
    Chris







  • 7.  RE: Synchronize portfolio investments job error - the datediff function resulted in an overflow

    Broadcom Employee
    Posted May 25, 2022 11:28 AM
    Hi Chris,
    Can you try moving the tasks under that summary task to see if you can get the summary to update?
    All the sub tasks have a good date but they are not rolling up correctly to the summary task.

    Besides that I am not sure how to troubleshoot the issue here.


    ------------------------------
    Liz Williamson
    Principal Support Engineer - Clarity
    Broadcom
    ------------------------------