question

TALENZ avatar image
TALENZ asked

Converting Decimal to Time

I have a column (Decimal, 18,0 Null) that has time values like the following: 20634 = 2:06:34 10349 = 1:03:49 52453 = 5:24:53 I've tried something like this: CONVERT(VARCHAR(5),DATEADD(MINUTE,(FLOOR((@Value / 1) / 100) * 60) + FLOOR((@Value / 1) % 100),CAST('00:00' AS TIME)),108) This doesn't work. Can anyone offer another suggestion? Thank you.
sql-server-2008sql server
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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

1 Answer

·
Kev Riley avatar image
Kev Riley answered
Try cast( left(right('0'+cast(@Value as varchar),6),2) + ':'+ left(right(cast(@Value as varchar),4),2) + ':'+ right(cast(@Value as varchar),2) as time)
5 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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

Kev Riley avatar image Kev Riley ♦♦ commented ·
then pad them out with leading 0s cast( left(right('000000'+cast(@Valueas varchar),6),2) + ':'+ left(right('000000'+cast(@Valueas varchar),4),2) + ':'+ right('000000'+cast(@Valueas varchar),2) as time)
3 Likes 3 ·
TALENZ avatar image TALENZ commented ·
I get a conversion error: Conversion failed when converting date and/or time from character string.
0 Likes 0 ·
TALENZ avatar image TALENZ commented ·
The length of my decimal values ranges from 1 to 6.
0 Likes 0 ·
TALENZ avatar image TALENZ commented ·
Thanks for your assistance!
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
You are welcome
0 Likes 0 ·

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.