Is there a better way to do this (padding Time field with zeros)

Transact-SQL
1

Hi guys,

Using T-SQL (SQL Server 2014)

We have a field which populates one of our tables which is a Time.

Example entries:
191000
194300
195100
170600
135500
140400
1100
133400
141100
103000
123600
160500

The entry of 1100 is eleven minutes past midnight.

I'd like to present these in a time format (i.e. 16:05:00 or 16:05:00.0000000) with padded leading zeros where necessary.

I originally used the stuff feature, but it got a bit messy when I had to consider short times such as 1100:

CASE WHEN LEN(Endtime) = 6 THEN cast(stuff(stuff(Endtime,3,0,':'),6,0,':')as time)
WHEN LEN(Endtime) = 5 THEN cast(stuff(stuff(stuff(Endtime,2,0,':'),5,0,':'),1,0,'0')as time)
WHEN LEN(Endtime) = 4 THEN cast(stuff(stuff(stuff(Endtime,1,0,':'),4,0,':'),1,0,'0')as time)
ELSE NULL END as 'test'

The above does work, but I'm wondering if there is a better/cleaner way of approaching this.

2

This seems to work for your data sample.

;with cte as
(
select a.Endtime 
from 
(
values
('191000'),
('194300'),
('195100'),
('170600'),
('135500'),
('140400'),
('1100'),
('133400'),
('141100'),
('103000'),
('123600'),
('160500')
) a(Endtime)
)

select cast(Replace(cast(cast(a.Endtime as numeric) / 10000 as decimal(6,2)), '.', ':') as time(0))   [test]

from cte a
3 
declare @table table (Endtime varchar(20))

insert into @table values
  ('191000')
 ,('194300')
 ,('195100')
 ,('170600')
 ,('135500')
 ,('140400')
 ,('1100')
 ,('133400')
 ,('141100')
 ,('103000')
 ,('123600')
 ,('160500')



select 
*,
case 
	when len(Endtime) = 4 then convert(time,replicate(0,2)+':'+left(Endtime,2))
	else convert(time,left(Endtime,2)+':'+substring(Endtime,3,2))
end as [TimeFormat]
from @table
4

STUFF is the way to go, just pad the column first so it's always 6 chars:

SELECT STUFF(STUFF(RIGHT('00000' + CAST(Endtime AS varchar(6)), 6), 5, 0, ':'), 3, 0, ':')

5

drop table enteries
create table enteries
(
times varchar(50)
)

insert into enteries values('191000')
insert into enteries values('194300')
insert into enteries values('195100')
insert into enteries values('170600')
insert into enteries values('135500')
insert into enteries values('140400')
insert into enteries values('1100')
insert into enteries values('133400')
insert into enteries values('141100')
insert into enteries values('103000')
insert into enteries values('123600')
insert into enteries values('160500')

select left(times,2)+':'+ substring(times,3,2)+':'+right(times,2)+':000000'from enteries