Using T-SQL (SQL Server 2014)
We have a field which populates one of our tables which is a Time.
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.