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?
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?
It seems that you have been asking questions on the forum but there is no feedback, and the suggested solution is not accepted as an answer.
This cannot help those who have similar problems and are looking for answers. In addition, this is unfair to those who spend time solving your problems.
Hi @cooolsweet-5113,
Could you please validate all the answers so far and provide any update?
If all are not working, please provide more sample data and expected output.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding!
Regards
Echo
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
Hi @cooolsweet-5113,
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.
10 people are following this question.