question

cooolsweet-5113 avatar image
0 Votes"
cooolsweet-5113 asked EchoLiu-msft commented

date format in tsql

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?

sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

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

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.