Converting varchar(6) in HHmmss format to sql time format

Question

How is it possible to convert something stored in DB as varchar(6) (e.g., 180000 which is actually in a format of HHmmss) to time, I tried the following but wont work

select convert(time,'180000')
select cast('180000' as time)

Answer 1

One more approach

DECLARE @t VARCHAR(6)='180000';

SELECT CAST(STUFF(STUFF(@t,5,0,':'),3,0,':') AS time)

Thanks to @SebtHU, who pointed out, that this would not work with leading zeros in times such as 09:15:00 or 00:45:00. You can use this instead:

SELECT CAST(STUFF(STUFF(RIGHT(CONCAT('000000',@t),6),5,0,':'),3,0,':') AS time);

Answer 2

try this :

select cast((substring('180000',0,3)+':'+substring('180000',3,2)+':'+substring('180000',5,2)) as time)