Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Format time string

Status
Not open for further replies.
Jun 9, 2006
159
US
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
 
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
 
Very Cool!!! Works like a charm.

Thanks.

Shawn Molloy
Seattle, WA
 
But will that work for the 12342 becoming 12:342? Won't it make it 123:42?

-SQLBill

Posting advice: FAQ481-4875
 
Nah, I tried a length value of 123456 and this works fine.

Shawn Molloy
Seattle, WA
 
However; I did find an error.

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

Code:
Invalid length parameter passed to the SUBSTRING function.

How can I fix this?

Shawn Molloy
Seattle, WA
 
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
 
Oops, you'll have to cast the concatenation of 0:0 + MyTimeCol into a varchar.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top