# question

## Converting Decimal to Time

I have a table with a data type of decimal for time values. For example, when the length is less than 6, I have 53112, 80703, and so on. When the length is greater than 6, I'll have 101423, 164147, and so on. I've searched high and low and I've not yet come up with a good solution. I've tried padding a 0 when the length is less than 6, but then I have difficulty parsing out the hours, minutes, and seconds.
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
I should add that I do not need to convert 1.5, for example, to 1 hour and 30 minutes. The values in the table are already actual time values except that they're in decimal form.
0 Likes 0 ·

·
Here's another way that is perhaps slightly more readable: ; with cte as ( select HourPart = left(CallTime,2 - (6 - len(CallTime))) , MinutePart = left(right(CallTime,4),2) , SecondPart = right(CallTime,2) from #text_times ) select CallTime = case when HourPart > 12 then cast(HourPart - 12 as varchar) else HourPart end + ':' + MinutePart + ':' + SecondPart + case when HourPart > 12 then ' PM' else ' AM' end from cte But there's nothing wrong with the way that you've done it.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I came up with this: ,CASE WHEN LEN(CallTime) < 6 THEN LEFT(CallTime,1) + ':' + RIGHT(LEFT(CallTime, 3),2) + ' AM' WHEN LEN(CallTime)>5 AND LEFT(CallTime, 2) < 12 THEN LEFT(CallTime, 2) + ':' + RIGHT(LEFT(CallTime, 4), 2) + ' AM' WHEN LEN(CallTime)>5 AND LEFT(CallTime, 2) >= 12 THEN CAST(LEFT(CallTime, 2) - 12 AS VARCHAR(6))+ ':' + RIGHT(LEFT(CallTime, 4), 2) + ' PM' END AS CallTime

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Try this : timefromparts ( Column/10000,(Column / 100) % 100,Column % 100,0,0);

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.