Return to Answer

Maybe SQL Server is making a terrible guess because the convert is happening inline. What if you used a variable or parameter (and stopped using ambiguous formats that require regional and other settings to be taken into account)?

DECLARE @d DATETIME = CONVERT(DATETIME, '20141227 00:26:24.137', 112); UPDATE DW_STAGE_PHOENIX.dbo.STG_PHX_LN_ACCT_INT_OPT SET ETL_JOB_SEQ_NUM = 1027140 , ETL_IUD_CD = 'D' , ETL_UPD_DTM = CONVERT(DATETIME, '20160316 01:01:17', 112) WHERE ETL_VERS_START_DTM = @d AND ACCT_NO = '5001194157' AND ACCT_TYPE = 'ILN'; 

It might even be better to always include a dummy clause to explicitly eliminate the archived partition:

AND ETL_VERS_START_DTM >= CONVERT(DATETIME, '19000101', 112) 

• Bad habits to kick : mis-handling date / range queries

Maybe SQL Server is making a terrible guess because the convert is happening inline. What if you used a variable or parameter (and stopped using ambiguous formats that require regional and other settings to be taken into account)?

DECLARE @d DATETIME = CONVERT(DATETIME, '20141227 00:26:24.137', 112); UPDATE DW_STAGE_PHOENIX.dbo.STG_PHX_LN_ACCT_INT_OPT SET ETL_JOB_SEQ_NUM = 1027140 , ETL_IUD_CD = 'D' , ETL_UPD_DTM = CONVERT(DATETIME, '20160316 01:01:17', 112) WHERE ETL_VERS_START_DTM = @d AND ACCT_NO = '5001194157' AND ACCT_TYPE = 'ILN'; 

It might even be better to always include a dummy clause to explicitly eliminate the archived partition:

AND ETL_VERS_START_DTM >= CONVERT(DATETIME, '19000101', 112) 

• Bad habits to kick : mis-handling date / range queries

Maybe SQL Server is making a terrible guess because the convert is happening inline. What if you used a variable or parameter (and stopped using ambiguous formats that require regional and other settings to be taken into account)?

DECLARE @d DATETIME = CONVERT(DATETIME, '20141227 00:26:24.137', 112); UPDATE DW_STAGE_PHOENIX.dbo.STG_PHX_LN_ACCT_INT_OPT SET ETL_JOB_SEQ_NUM = 1027140 , ETL_IUD_CD = 'D' , ETL_UPD_DTM = CONVERT(DATETIME, '20160316 01:01:17') WHERE ETL_VERS_START_DTM = @d AND ACCT_NO = '5001194157' AND ACCT_TYPE = 'ILN'; 

It might even be better to always include a dummy clause to explicitly eliminate the archived partition:

AND ETL_VERS_START_DTM >= CONVERT(DATETIME, '19000101', 112) 

• Bad habits to kick : mis-handling date / range queries

Maybe SQL Server is making a terrible guess because the convert is happening inline. What if you used a variable or parameter (and stopped using ambiguous formats that require regional and other settings to be taken into account)?

DECLARE @d DATETIME = CONVERT(DATETIME, '20141227 00:26:24.137', 112); UPDATE DW_STAGE_PHOENIX.dbo.STG_PHX_LN_ACCT_INT_OPT SET ETL_JOB_SEQ_NUM = 1027140 , ETL_IUD_CD = 'D' , ETL_UPD_DTM = CONVERT(DATETIME, '20160316 01:01:17', 112) WHERE ETL_VERS_START_DTM = @d AND ACCT_NO = '5001194157' AND ACCT_TYPE = 'ILN'; 

It might even be better to always include a dummy clause to explicitly eliminate the archived partition:

AND ETL_VERS_START_DTM >= CONVERT(DATETIME, '19000101', 112) 

• Bad habits to kick : mis-handling date / range queries

Maybe SQL Server is making a terrible guess because the convert is happening inline. What if you used a variable or parameter (and stopped using ambiguous formats that require regional and other settings to be taken into account)?

DECLARE @d DATETIME = '20141227 00:26:24.137'; UPDATE DW_STAGE_PHOENIX.dbo.STG_PHX_LN_ACCT_INT_OPT SET ETL_JOB_SEQ_NUM = 1027140 , ETL_IUD_CD = 'D' , ETL_UPD_DTM = CONVERT(DATETIME, '20160316 01:01:17') WHERE ETL_VERS_START_DTM = @d AND ACCT_NO = '5001194157' AND ACCT_TYPE = 'ILN'; 

• Bad habits to kick : mis-handling date / range queries

Maybe SQL Server is making a terrible guess because the convert is happening inline. What if you used a variable or parameter (and stopped using ambiguous formats that require regional and other settings to be taken into account)?

DECLARE @d DATETIME = CONVERT(DATETIME, '20141227 00:26:24.137', 112); UPDATE DW_STAGE_PHOENIX.dbo.STG_PHX_LN_ACCT_INT_OPT SET ETL_JOB_SEQ_NUM = 1027140 , ETL_IUD_CD = 'D' , ETL_UPD_DTM = CONVERT(DATETIME, '20160316 01:01:17') WHERE ETL_VERS_START_DTM = @d AND ACCT_NO = '5001194157' AND ACCT_TYPE = 'ILN'; 

It might even be better to always include a dummy clause to explicitly eliminate the archived partition:

AND ETL_VERS_START_DTM >= CONVERT(DATETIME, '19000101', 112) 

• Bad habits to kick : mis-handling date / range queries