sqlsql-server-2012time-format

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

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

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