title: Convert Function takes long time for execution in SQL Server
body: <p>We have a query which contains 15 <code>convert</code> function calls against different datetime columns, along with 20 other columns. The query takes nearly 5 minutes when executed with <code>Convert</code> functions, however when we comment out/remove convert functions it takes only 15 seconds.</p>
  
  <pre><code>CONVERT (VARCHAR (10), TABLE.[COLUMN NAME], 101)
  
  
  SELECT
        T1.CONFLICT_ID,
        CONVERT (VARCHAR (10),T1.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T1.LAST_UPD, 8),
        CONVERT (VARCHAR (10),T1.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T1.CREATED, 8),
        T1.LAST_UPD_BY,
        T1.CREATED_BY,
        T1.MODIFICATION_NUM,
        T1.ROW_ID,
        CONVERT (VARCHAR (10),T1.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T1.CREATED, 8),
        T1.ROW_ID,
        CONVERT (VARCHAR (10),T1.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T1.LAST_UPD, 8),
        T1.LAST_UPD_BY,
        CONVERT (VARCHAR (10),T1.TODO_AFTER_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_AFTER_DT, 8),
        T1.APPT_REPT_TYPE,
        T27.ATTRIB_47,
        T1.ROW_STATUS,
        T24.OWN_INST_ID,
        T24.INTEGRATION_ID,
        T15.SR_TITLE,
        T27.ATTRIB_45,
        T1.APPT_ALARM_TM_MIN,
        T15.SR_NUM,
        T1.PR_PRDINT_ID,
        T1.PR_SR_ID,
        T1.PR_SYMPTOM_CD,
        CONVERT (VARCHAR (10),T13.PLAN_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T13.PLAN_END_DT, 8),
        CONVERT (VARCHAR (10),T13.PLAN_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T13.PLAN_START_DT, 8),
        T12.BL_CLASS_CD,
        T12.BL_CURCY_CD,
        T1.PROJ_ID,
        CONVERT (VARCHAR (10),T13.ACTL_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T13.ACTL_END_DT, 8),
        CONVERT (VARCHAR (10),T13.ACTL_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T13.ACTL_START_DT, 8),
        T9.NAME,
        T9.CURCY_CD,
        T1.OPTY_ID,
        T10.BASE_CURCY_CD,
        T1.TARGET_OU_ID,
        T10.LOC,
        T10.NAME,
        CONVERT (VARCHAR (10),T1.TODO_ACTL_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_ACTL_END_DT, 8),
        T1.SRV_REGN_ID,
        T16.NAME,
        T18.AMS_ROUTE_TO_CD,
        T1.ME_EVT_ID,
        T1.ASGN_MANL_FLG,
        T20.MSTR_CASE_ID,
        T22.NUM_PAGE,
        T18.AMS_ACT_ID,
        T19.MAX_CAPABILITY_LVL,
        T19.MIN_CAPABILITY_LVL,
        T19.RTE_APPR_FLG,
        T1.OWNER_LOGIN,
        T2.BU_ID,
        T1.OWNER_PER_ID,
        T1.APPT_REPT_FLG,
        CONVERT (VARCHAR (10),T1.APPT_REPT_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_REPT_END_DT, 8),
        T1.PR_ORDER_ID,
        T1.DO_NOT_ROUTE_FLG,
        T1.TARGET_OU_ADDR_ID,
        T1.ARREST_ID,
        T1.ASSET_ID,
        T1.ASGN_USR_EXCLD_FLG,
        T7.PR_POSTN_ID,
        T28.PIM_APPT_FLG,
        T28.SEBL_APPT_FLG,
        T28.UNSPRTD_REPT_FLG,
        T26.SRM_REQUEST_ID,
        T12.NAME,
        T12.PROJ_NUM,
        T1.X_ENTITY_TYPE,
        T1.XTRNL_ACCESS_FLG,
        T29.EVIDENCE_ID,
        T1.SRA_TYPE_CD,
        T1.COMM_ID,
        T1.COMMENTS_LONG,
        T1.COMMENTS,
        T10.PR_ADDR_ID,
        T1.PR_PROD_CAT_ID,
        T3.FST_NAME,
        T1.TARGET_PER_ID,
        T3.LAST_NAME,
        T1.OWNER_OU_ID,
        T1.COMM_ID,
        T1.CMPLTD_FLG,
        T20.CASE_NUM,
        T1.X_OFFENSE_ID,
        T27.ATTRIB_37,
        T1.PREV_ACT_ID,
        CONVERT (VARCHAR (10),T1.APPT_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_END_DT, 8),
        CONVERT (VARCHAR (10),T1.APPT_END_TM, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_END_TM, 8),
        T14.SCHED_ENGINE_CD,
        T6.ASSET_NUM,
        T11.NAME,
        T1.OWNER_POSTN_ID,
        T25.WEB_ACCESS_FLG,
        T1.SRC_ID,
        T1.X_CASE_ID,
        T20.NAME,
        CONVERT (VARCHAR (10),T1.TODO_DUE_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_DUE_DT, 8),
        T1.APPT_DURATION_MIN,
        CONVERT (VARCHAR (10),T18.TLR_EFFECTIVE_DT, 101) + ' ' + CONVERT (VARCHAR (10),T18.TLR_EFFECTIVE_DT, 8),
        T26.ACTIVE_FLG,
        T19.NAME,
        T1.SRA_DEFECT_ID,
        T1.SRA_SR_ID,
        T1.TMPL_PLANITEM_ID,
        T1.ACTIVITY_UID,
        T1.AGREEMENT_ID,
        T1.PYMNT_FLG,
        T1.ALARM_FLAG,
        T1.CREATOR_LOGIN,
        T1.ASGN_DNRM_FLG,
        T1.NAME,
        T18.TLR_INTG_RET_CD,
        T1.INCIDENT_ID,
        T8.X_SESSION_ID,
        T18.TLR_INTG_ID,
        T18.TLR_INTG_MSG,
        CONVERT (VARCHAR (10),T1.TODO_ACTL_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_ACTL_END_DT, 8),
        T1.DONE_FLG,
        CONVERT (VARCHAR (10),T1.APPT_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_START_DT, 8),
        T4.POSTN_TYPE_CD,
        CONVERT (VARCHAR (10),T1.STATUS_UPD_TS, 101) + ' ' + CONVERT (VARCHAR (10),T1.STATUS_UPD_TS, 8),
        T10.DIVISION,
        T1.COMMENTS,
        T26.STAGE_ID,
        CONVERT (VARCHAR (10),T1.APPT_START_TM, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_START_TM, 8),
        CONVERT (VARCHAR (10),T1.TODO_ACTL_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_ACTL_START_DT, 8),
        T1.ASSESS_7,
        T1.X_AUTO_FLG,
        T1.X_SEQUENCE,
        T23.FORMAT_TYPE_CD,
        T1.X_OFFENSE_ID,
        T5.EMP_ID,
        T21.SRV_REGN_ID,
        T2.FST_NAME,
        T2.LAST_NAME,
        T14.SCHED_PARAM_SET_ID,
        T1.PAR_EVT_ID,
        T1.BILLABLE_FLG,
        T1.APPT_REPT_APPT_ID,
        T1.APPT_REPT_REPL_CD,
        T1.TEMPLATE_FLG,
        T1.ASGN_SYS_FLG,
        T1.PCT_COMPLETE,
        T1.TARGET_PER_ADDR_ID,
        T18.SUB_TYPE_CD,
        T1.CL_SUBJ_ID,
        T1.CAL_DISP_FLG,
        CONVERT (VARCHAR (10),T1.TODO_PLAN_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_PLAN_START_DT, 8),
        CONVERT (VARCHAR (10),T1.TODO_PLAN_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_PLAN_END_DT, 8),
        T1.COST_CURCY_CD,
        T15.SR_STAT_ID,
        T1.SUBTYPE_CD,
        T1.TODO_CD,
        T20.X_CASE_MODE,
        T1.CAL_TYPE_CD,
        T1.EVT_STAT_CD,
        T30.ROW_ID,
        T31.ROW_STATUS,
        T33.AMS_CAPABILITY_LVL,
        T34.LOGIN,
        T32.ROW_ID,
        T36.GEOCD_VALID_FLG,
        T22.ROW_ID,
        T22.PAR_ROW_ID,
        T22.MODIFICATION_NUM,
        T22.CREATED_BY,
        T22.LAST_UPD_BY,
        CONVERT (VARCHAR (10),T22.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T22.CREATED, 8),
        CONVERT (VARCHAR (10),T22.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T22.LAST_UPD, 8),
        T22.CONFLICT_ID,
        T22.PAR_ROW_ID,
        T18.ROW_ID,
        T18.PAR_ROW_ID,
        T18.MODIFICATION_NUM,
        T18.CREATED_BY,
        T18.LAST_UPD_BY,
        CONVERT (VARCHAR (10),T18.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T18.CREATED, 8),
        CONVERT (VARCHAR (10),T18.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T18.LAST_UPD, 8),
        T18.CONFLICT_ID,
        T18.PAR_ROW_ID,
        T23.ROW_ID,
        T23.PAR_ROW_ID,
        T23.MODIFICATION_NUM,
        T23.CREATED_BY,
        T23.LAST_UPD_BY,
        CONVERT (VARCHAR (10),T23.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T23.CREATED, 8),
        CONVERT (VARCHAR (10),T23.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T23.LAST_UPD, 8),
        T23.CONFLICT_ID,
        T23.PAR_ROW_ID,
        T24.ROW_ID,
        T24.PAR_ROW_ID,
        T24.MODIFICATION_NUM,
        T24.CREATED_BY,
        T24.LAST_UPD_BY,
        CONVERT (VARCHAR (10),T24.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T24.CREATED, 8),
        CONVERT (VARCHAR (10),T24.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T24.LAST_UPD, 8),
        T24.CONFLICT_ID,
        T24.PAR_ROW_ID,
        T25.ROW_ID,
        T25.PAR_ROW_ID,
        T25.MODIFICATION_NUM,
        T25.CREATED_BY,
        T25.LAST_UPD_BY,
        CONVERT (VARCHAR (10),T25.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T25.CREATED, 8),
        CONVERT (VARCHAR (10),T25.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T25.LAST_UPD, 8),
        T25.CONFLICT_ID,
        T25.PAR_ROW_ID,
        T26.ROW_ID,
        T26.PAR_ROW_ID,
        T26.MODIFICATION_NUM,
        T26.CREATED_BY,
        T26.LAST_UPD_BY,
        CONVERT (VARCHAR (10),T26.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T26.CREATED, 8),
        CONVERT (VARCHAR (10),T26.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T26.LAST_UPD, 8),
        T26.CONFLICT_ID,
        T26.PAR_ROW_ID,
        T27.ROW_ID,
        T27.PAR_ROW_ID,
        T27.MODIFICATION_NUM,
        T27.CREATED_BY,
        T27.LAST_UPD_BY,
        CONVERT (VARCHAR (10),T27.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T27.CREATED, 8),
        CONVERT (VARCHAR (10),T27.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T27.LAST_UPD, 8),
        T27.CONFLICT_ID,
        T27.PAR_ROW_ID,
        T28.ROW_ID,
        T28.PAR_ROW_ID,
        T28.MODIFICATION_NUM,
        T28.CREATED_BY,
        T28.LAST_UPD_BY,
        CONVERT (VARCHAR (10),T28.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T28.CREATED, 8),
        CONVERT (VARCHAR (10),T28.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T28.LAST_UPD, 8),
        T28.CONFLICT_ID,
        T28.PAR_ROW_ID,
        T29.ROW_ID,
        T29.PAR_ROW_ID,
        T29.MODIFICATION_NUM,
        T29.CREATED_BY,
        T29.LAST_UPD_BY,
        CONVERT (VARCHAR (10),T29.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T29.CREATED, 8),
        CONVERT (VARCHAR (10),T29.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T29.LAST_UPD, 8),
        T29.CONFLICT_ID,
        T29.PAR_ROW_ID,
        T30.ROW_ID,
        T31.ROW_ID,
        T32.ROW_ID,
        T35.ROW_ID,
        T36.ROW_ID
        FROM 
         dbo.S_EVT_ACT T1 
            LEFT OUTER JOIN dbo.S_CONTACT T2 ON T1.OWNER_PER_ID = T2.PAR_ROW_ID
            LEFT OUTER JOIN dbo.S_CONTACT T3 ON T1.TARGET_PER_ID = T3.PAR_ROW_ID
            LEFT OUTER JOIN dbo.S_POSTN T4 ON T2.PR_HELD_POSTN_ID = T4.ROW_ID
            LEFT OUTER JOIN dbo.S_ACT_EMP T5 ON T1.ROW_ID = T5.ACTIVITY_ID
            LEFT OUTER JOIN dbo.S_ASSET T6 ON T1.ASSET_ID = T6.ROW_ID
            LEFT OUTER JOIN dbo.S_CONTACT T7 ON T1.TARGET_PER_ID = T7.PAR_ROW_ID
            LEFT OUTER JOIN dbo.S_INCIDENT T8 ON T1.INCIDENT_ID = T8.ROW_ID
            LEFT OUTER JOIN dbo.S_OPTY T9 ON T1.OPTY_ID = T9.ROW_ID
            LEFT OUTER JOIN dbo.S_ORG_EXT T10 ON T1.TARGET_OU_ID = T10.PAR_ROW_ID
            LEFT OUTER JOIN dbo.S_POSTN T11 ON T1.OWNER_POSTN_ID = T11.PAR_ROW_ID
            LEFT OUTER JOIN dbo.S_PROJ T12 ON T1.PROJ_ID = T12.ROW_ID
            LEFT OUTER JOIN dbo.S_PROJITEM T13 ON T1.PROJ_ITEM_ID = T13.ROW_ID
            LEFT OUTER JOIN dbo.S_SRV_REGN T14 ON T1.SRV_REGN_ID = T14.ROW_ID
            LEFT OUTER JOIN dbo.S_SRV_REQ T15 ON T1.SRA_SR_ID = T15.ROW_ID
            LEFT OUTER JOIN dbo.S_TIMEZONE T16 ON T14.TIME_ZONE_ID = T16.ROW_ID
            LEFT OUTER JOIN dbo.S_OPTY_POSTN T17 ON T1.OPTY_ID = T17.OPTY_ID AND T17.POSITION_ID = '1-D38P'
            LEFT OUTER JOIN dbo.S_EVT_ACT_FNX T18 ON T1.ROW_ID = T18.PAR_ROW_ID
            LEFT OUTER JOIN dbo.S_FN_APPR T19 ON T18.AMS_ACT_ID = T19.ROW_ID
            LEFT OUTER JOIN dbo.S_CASE T20 ON T1.X_CASE_ID = T20.ROW_ID
            LEFT OUTER JOIN dbo.S_EMP_PER T21 ON T1.OWNER_PER_ID = T21.PAR_ROW_ID
            LEFT OUTER JOIN dbo.S_EVT_ACT_LSX T22 ON T1.ROW_ID = T22.PAR_ROW_ID
            LEFT OUTER JOIN dbo.S_EVT_MAIL T23 ON T1.ROW_ID = T23.PAR_ROW_ID
            LEFT OUTER JOIN dbo.S_EVT_ACT_SS T24 ON T1.ROW_ID = T24.PAR_ROW_ID
            LEFT OUTER JOIN dbo.S_SUSP_ACT T25 ON T1.ROW_ID = T25.PAR_ROW_ID
            LEFT OUTER JOIN dbo.S_EVT_MKTG T26 ON T1.ROW_ID = T26.PAR_ROW_ID
            LEFT OUTER JOIN dbo.S_EVT_ACT_X T27 ON T1.ROW_ID = T27.PAR_ROW_ID
            LEFT OUTER JOIN dbo.S_EVT_CAL T28 ON T1.ROW_ID = T28.PAR_ROW_ID
            LEFT OUTER JOIN dbo.S_EVT_CASE T29 ON T1.ROW_ID = T29.PAR_ROW_ID
            LEFT OUTER JOIN dbo.S_PARTY T30 ON T1.TARGET_PER_ID = T30.ROW_ID
            LEFT OUTER JOIN dbo.S_ACT_EMP T31 ON T1.OWNER_PER_ID = T31.EMP_ID AND T1.ROW_ID = T31.ACTIVITY_ID
            LEFT OUTER JOIN dbo.S_PARTY T32 ON T31.EMP_ID = T32.ROW_ID
            LEFT OUTER JOIN dbo.S_CONTACT_FNX T33 ON T31.EMP_ID = T33.PAR_ROW_ID
            LEFT OUTER JOIN dbo.S_USER T34 ON T31.EMP_ID = T34.PAR_ROW_ID
            LEFT OUTER JOIN dbo.S_CON_ADDR T35 ON T1.TARGET_OU_ADDR_ID = T35.ADDR_PER_ID AND T1.TARGET_OU_ID = T35.ACCNT_ID
            LEFT OUTER JOIN dbo.S_ADDR_PER T36 ON T1.TARGET_OU_ADDR_ID = T36.ROW_ID
     WHERE 
        ((T1.APPT_REPT_REPL_CD IS NULL) AND
        ((T1.TEMPLATE_FLG != N'Y' AND T1.TEMPLATE_FLG != N'P' OR T1.TEMPLATE_FLG IS NULL) AND (T1.OPTY_ID IS NULL OR T9.SECURE_FLG = N'N' OR T17.OPTY_ID IS NOT NULL)) AND
        (T1.PRIV_FLG = 'N' OR T1.PRIV_FLG IS NULL OR T1.OWNER_PER_ID = '1-K6JK')) AND
        (T1.ROW_ID = '1-Y95E')
  </code></pre>