date format in tsql

Question

hi

i have column values as ' wed, 7 Apr 2021 14:42:25', i need to convert it
as 2021-04-07 14:42:25.

how to do it?

Answer 1

Hi @coool sweet ,

Please refer to:

CREATE TABLE #test(dates char(25))  
INSERT INTO  #test VALUES('wed, 7 Apr 2021 14:42:25')  
  
SELECT FORMAT(CAST(RIGHT(dates,LEN(dates)-CHARINDEX(',',dates)) as datetime2),'yyyy-MM-dd HH:mm:ss')  
FROM #test  

Output:

2021-04-07 14:42:25  

If you want to update the column, then:

UPDATE #test  
SET dates=FORMAT(CAST(RIGHT(dates,LEN(dates)-CHARINDEX(',',dates)) as datetime2),'yyyy-MM-dd HH:mm:ss')  
FROM #test  

If you have any question, please feel free to let me know.

Regards
Echo

---

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

Answer 2

If you want to convert a string column like ‘varchar(max)’, then try something like this:

update t
set MyColumn = convert(varchar(max), c, 120)
from MyTable t
cross apply (values (try_convert(datetime, substring(ltrim(MyColumn), 5, 100), 113))) d(c)
where len(MyColumn) > 4 and  c is not null