SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE T(
  MyCol VARCHAR(10)
);

INSERT INTO T(MyCol) VALUES
('01012018'),
('01022018'),
('01032018'),
('WrongData');

SELECT MyCol
FROM T;


MyCol


01012018

01022018

01032018

WrongData


UPDATE T
SET MyCol = TRY_CONVERT(DATE,
                        CONCAT( RIGHT(MyCol, 4),
                                SUBSTRING(MyCol, 3, 2),
                                LEFT(MyCol, 2)
                              )
                        );
4 rows affected

ALTER TABLE T
ALTER COLUMN MyCol DATE;
SELECT MyCol
FROM T;


MyCol


01/01/2018 00:00:00

01/02/2018 00:00:00

01/03/2018 00:00:00

null