DesNorton - Thursday, October 19, 2017 11:19 AM

GrassHopper - Thursday, October 19, 2017 10:04 AM

I used your code in parts.  I am getting error msg's when trying to update the dates with dots. i.e. 21.03.17  
error msg:
Conversion failed when converting date and/or time from character string.

Code I am using to convert t

Select PTL_StartDate, PTL_EndDate
    , Case WHEN PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' AND RIGHT(RTrim(PTL_StartDate),4) NOT IN ('2105','2106','0216') THEN CONVERT(date, PTL_StartDate, 104)  -- 27.01.2014
         WHEN PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9]' AND RIGHT(RTRIM(PTL_StartDate),2) NOT IN('32') THEN CONVERT(date, PTL_StartDate, 4)  -- 27.07.16
  Else '01/01/1900'
  END as startdate_2
FROM XLSHdr_Promo_TescoUK
Where (PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' OR PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9]')
AND RIGHT(RTrim(PTL_StartDate),4) NOT IN ('2105','2106','0216') AND RIGHT(RTRIM(PTL_StartDate),2) NOT IN('32')

I suspect that SQL is attempting to convert all the underlying data before filtering the results.
Try something like this
SELECT PTL_StartDate, PTL_EndDate
INTO #Staging
FROM XLSHdr_Promo_TescoUK
WHERE (PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' OR PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9]')
AND RIGHT(RTrim(PTL_StartDate),4) NOT IN ('2105','2106','0216')
AND RIGHT(RTRIM(PTL_StartDate),2) NOT IN('32');

Select PTL_StartDate, PTL_EndDate
, CASE WHEN PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' AND RIGHT(RTrim(PTL_StartDate),4) NOT IN ('2105','2106','0216') THEN CONVERT(date, PTL_StartDate, 104) -- 27.01.2014
   WHEN PTL_StartDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9]' AND RIGHT(RTRIM(PTL_StartDate),2) NOT IN('32') THEN CONVERT(date, PTL_StartDate, 4) -- 27.07.16
   ELSE CONVERT(date, '01/01/1900', 101)
  END as startdate_2
FROM #Staging;