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)

Solution 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);

Solution 2

try this :

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

Comments

[SebTHU]

NB this won't work with "no leading zeros" values like 91500 for 09:15:00, or 4500 for 00:45:00. (msdb.dbo.sysjobschedules, for example, is full of these). Need to add zeros on first e.g. with REPLICATE.

[Shnugo]

@SebTHU, thanks, you are right. I suggest to use something along RIGHT(CONCAT('000000',@t),6)