Format time string

[ShawnMolloy]

I have an int column that holds a time length data from a music track. Its recording the data like this:

2444
123
12342
23

and I would like to format it as such:

24:44
1:23
12:342
:23

Can anyone offer me a way of doign this?

Shawn Molloy
Seattle, WA

 

[vbkris]

try:

select
left(cast(MyTimeCol as varchar),len(cast(MyTimeCol as varchar))-2)+':'+
right(cast(MyTimeCol as varchar),2)
from
MyTable

Known is handfull, Unknown is worldfull

 

[ShawnMolloy]

Very Cool!!! Works like a charm.

Thanks.

Shawn Molloy
Seattle, WA

 

[SQLBill]

But will that work for the 12342 becoming 12:342? Won't it make it 123:42?

-SQLBill

Posting advice: FAQ481-4875

 

[ShawnMolloy]

Nah, I tried a length value of 123456 and this works fine.

Shawn Molloy
Seattle, WA

 

[ShawnMolloy]

However; I did find an error.

If the value is 1 character long it will return this error:

Invalid length parameter passed to the SUBSTRING function.

How can I fix this?

Shawn Molloy
Seattle, WA

 

[kxramse]

Throw this into a case statement and do a length check on the field. When its 1, concatenate 0:0 + x

I wrote this in Psuedocode cause I gotta run:
case MyTimeCol
when length <=1
value = 0:0 + MyTimeCol
else
value = left(cast(MyTimeCol as varchar),len(cast(MyTimeCol as varchar))-2)+':'+
right(cast(MyTimeCol as varchar),2)
end case

case statement syntax is in the BOL.

Take care,
Keith

 

[kxramse]

Oops, you'll have to cast the concatenation of 0:0 + MyTimeCol into a varchar.