How to convert varchar to Date in SQL

You can convert it directly with the CONVERT function by using the style 112 = ISO = yyyymmdd:

DECLARE @date int;

SET @date = 20070701

SELECT CONVERT(datetime, CONVERT(varchar(8), @date), 112)

SELECT CONVERT(datetime, '20070701', 112)

You can get this error Arithmetic overflow error converting expression to data type datetime.

that error came because we were comparing a datetime with int without converting later to datetime.

like

X.PriceDt < A.PrimaryDate

this will fix this

X.ClosePriceDate < CONVERT(datetime, CONVERT(varchar(8), A.PrimaryDate), 112))