sql - Convert varchar to time

Question

I have tried the links available here for similar questions before but it does not work. So I am posting my case here.

I have time in format:

09:47:11:895799

This is stored as varchar(50) in my Microsoft SQL Server database.

I am trying to convert it to time format as on this link.

hh:mm:ss[.nnnnnnn]

Can you please help?

Answer 1

The string is in the wrong format. It should be 09:47:11.895799. You need to replace the final : with a .

SELECT CAST(STUFF('09:47:11:895799', 9,1,'.') AS TIME)