question

TALENZ avatar image
TALENZ asked

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.
t-sqlsql-server
1 comment
10 |1200

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

TALENZ avatar image TALENZ commented ·
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 ·
iainrobertson avatar image
iainrobertson answered
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.
10 |1200

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

TALENZ avatar image
TALENZ answered
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
10 |1200

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

Conan avatar image
Conan answered
Try this : timefromparts ( Column/10000,(Column / 100) % 100,Column % 100,0,0);
10 |1200

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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