convert multiple date format in the column

Question

Hi Expert,,
i have multiple timestamp format value in a column how to convert it to_timestamp condition

column1
23/12/2022
12/22/2021
23-12-2022

Best Regards,

Answer 1

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.

Answer 2

Hi @Shambhu Rai
Maybe you can use below code:

SELECT   
 TO_TIMESTAMP(REPLACE(column1,'-','/'), 'MM/dd/yyyy') as var3  
FROM table;  

Best Regards,
Isabella

Answer 3

Since you expect 'mm/dd/yyyy' by default, then try COALESCE and TRY_CONVERT:

select *,  
   coalesce(try_convert(date, column1, 101), try_convert(date, column1, 103)) as converted  
from MyTable  

The to_timestamp function seems to be missing in SQL Server. Do you want to convert the date to something else?

Answer 4

Hi @Shambhu Rai ,

From SSIS side, you may use Data Conversion to covert the column to datetimestamp.

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 5

You need to clean your dirty datetime data in your database. There is no sure solution for any conversion if you mixed different format in a string type column. Clean them first with your business rules to store data in a proper data type, for example, a date type column to store your date values.