Hi @Shambhu Rai
Check this query:
DECLARE @dateBusters TABLE (badDate VARCHAR(10))
INSERT INTO @dateBusters (badDate) VALUES
('20140721'),('31.07.2014'),('07.04.2015'),('20150108'),('20140811'),
('20150216'),('7/21/2014'),('11.08.2014'),('07.08.2014'),('23/12/2022'),
('12/22/2021'),('23-12-2022'),('10/11/2021')
;WITH paddedDate AS
(
SELECT badDate, RIGHT('00000000' + REPLACE(REPLACE(REPLACE(badDate,'.',''),'/',''),'-',''),8) AS padded
FROM @dateBusters
),fixDate AS
(
SELECT badDate
,CASE WHEN ISDATE(badDate) = 1 THEN CAST(badDate AS DATE) ELSE NULL END AS tryCast
,CAST(
CASE WHEN LEFT(padded,4) BETWEEN 1900 AND 2100 THEN LEFT(padded,4)
WHEN LEFT(padded,4) NOT BETWEEN 1900 AND 2100 THEN RIGHT(padded,4)
END +'-'+
CASE WHEN LEFT(padded,4) BETWEEN 1900 AND 2100
THEN CASE WHEN RIGHT(LEFT(padded,6),2) > 12
THEN RIGHT(padded,2) ELSE RIGHT(LEFT(padded,6),2) END
WHEN LEFT(padded,4) NOT BETWEEN 1900 AND 2100
THEN CASE WHEN LEFT(RIGHT(padded,6),2) > 12
THEN LEFT(padded,2) ELSE LEFT(RIGHT(padded,6),2) END
END +'-'+
CASE WHEN LEFT(padded,4) BETWEEN 1900 AND 2100
THEN CASE WHEN RIGHT(LEFT(padded,6),2) > 12
THEN RIGHT(LEFT(padded,6),2) ELSE RIGHT(padded,2) END
WHEN LEFT(padded,4) NOT BETWEEN 1900 AND 2100
THEN CASE WHEN LEFT(RIGHT(padded,6),2) > 12
THEN LEFT(RIGHT(padded,6),2) ELSE LEFT(padded,2) END
END AS DATE) AS peice
FROM paddedDate
)
SELECT badDate, COALESCE(tryCast,peice) AS fixedDate
FROM fixDate
Referenced from Patrick Hurst's answer in this thread: How to load the multiple date format column date into the SQL Table Using ssis?
Best regards,
LiHong
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.