GrassHopper - Friday, October 20, 2017 2:55 PM
A loop like this will do the trick. You can keep changing the WHERE clause to do different batchesCREATE TABLE #Staging (
ID INT NOT NULL
, PTL_EndDate VARCHAR(20)
, Clean_PTL_EndDate DATE
);
INSERT INTO #Staging (ID, PTL_EndDate)
VALUES ( 117915, '27.01.2014' )
, ( 117916, '15.27.2014' )
, ( 117916, '01.27.14' )
, ( 117917, '2014.01.27' )
, ( 118301, '27.01.2014' )
, ( 118302, '01.27.2014' );
DECLARE @BatchSize INT = 1; -- Start with a big number, and keep making it smaller
BEGIN TRY
WHILE EXISTS (SELECT 1 FROM #Staging
WHERE Clean_PTL_EndDate IS NULL
AND (PTL_EndDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]'
OR PTL_EndDate LIKE '[0-1][0-9].[0-3][0-9].[0-9][0-9][0-9][0-9]')
)
BEGIN
WITH cteBatch AS (
SELECT TOP (@BatchSize)
ID
, PTL_EndDate
, Clean_PTL_EndDate
, YearPart = PARSENAME(PTL_EndDate, 1)
, MonthPart = CASE WHEN PARSENAME(PTL_EndDate, 2) > 12 THEN PARSENAME(PTL_EndDate, 3) ELSE PARSENAME(PTL_EndDate, 2) END
, DayPart = CASE WHEN PARSENAME(PTL_EndDate, 2) > 12 THEN PARSENAME(PTL_EndDate, 2) ELSE PARSENAME(PTL_EndDate, 3) END
FROM #Staging
WHERE Clean_PTL_EndDate IS NULL
AND (PTL_EndDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]'
OR PTL_EndDate LIKE '[0-1][0-9].[0-3][0-9].[0-9][0-9][0-9][0-9]')
ORDER BY ID
)
UPDATE cteBatch
SET Clean_PTL_EndDate = CONVERT(date, DayPart + '.' + MonthPart + '.' + YearPart, 104);
WAITFOR DELAY '00:00:00.100'; -- Pause for 100ms
END;
END TRY
BEGIN CATCH
-- Somewhere in this batch you should find your bad apple.
-- If this returns no data, then the specific pattern has been taken care of
SELECT TOP (@BatchSize) *
FROM #Staging
WHERE Clean_PTL_EndDate IS NULL
AND (PTL_EndDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]'
OR PTL_EndDate LIKE '[0-1][0-9].[0-3][0-9].[0-9][0-9][0-9][0-9]')
ORDER BY ID;
END CATCH