TSQL Convert VARCHAR of HHMMSS to TIME

Question

I have a VARCHAR field that has sample values as listed below and I would like to convert it to HH:MM:SS. Suggestions?

151200
085800
080100
210100
083300

Answer 1

Declare @YourTable table (TimeStr varchar(25))
Insert Into @YourTable values
('151200'),
('085800'),
('080100'),
('210100'),
('083300')


Select *
      ,AsTime = cast(stuff(stuff(TimeStr,5,0,':'),3,0,':') as time)
 From @YourTable

Returns

TimeStr AsTime
151200  15:12:00.0000000
085800  08:58:00.0000000
080100  08:01:00.0000000
210100  21:01:00.0000000
083300  08:33:00.0000000

---

EDIT:

Depending on your version an considering you are storing time values in a varchar (which means you could have bogus data), you could use:

try_convert(time,stuff(stuff(TimeStr,5,0,':'),3,0,':'))

Answer 2

If you are using SQL Server 2012 or above and want to store the values as actual TIME data types, you can use the TIMEFROMPARTS function.

For example:

DECLARE @d VARCHAR(10)
SET @d = '151200'

SELECT TIMEFROMPARTS(LEFT(@d,2), SUBSTRING(@d,3,2), RIGHT(@d,2), 0)

This will return a TIME. For more information, please see this: https://msdn.microsoft.com/en-us/library/hh213398.aspx

Answer 3

On Sql-Server try this:

declare @t varchar(10) 
set @t='151200'

select left(@t,2) + ':' + substring(@t,3,2) + ':' + right(@t,2)

Answer 4

If the time is stored as an INT the following works well on SQL Server 2019:

DECLARE @time INT = 912;

SELECT TRY_CONVERT(time, FORMAT(@time, '00:00:00'))

This also allows to extract seconds with the following

SELECT DATEDIFF(second, 0, TRY_CONVERT(time, FORMAT(@time, '00:00:00'))) 

And, when working with msdb.dbo.sysjobhistory that for some reason stores run_duration as an INT in this format hhmmss

SELECT duration_seconds = DATEDIFF(second, 0, TRY_CONVERT(time, FORMAT(run_duration, '00:00:00')))
,duration_time = TRY_CONVERT(time, FORMAT(run_duration, '00:00:00'))
, * FROM msdb.dbo.sysjobhistory