how to get some substring in sql separate of ":" with the simplest query?

Question

this is my variable

 declare @fecha varchar(50)
 set @fecha='29:14:2'
 select 
horas= CONVERT(int,SUBSTRING (@fecha,1,
 charindex(':',@fecha)-1))

**@fecha could to have this format

set @fecha='9:4:2' or set @fecha='29:59:59'

i need get hour=24, minutes=14, second=2 with the simplest select

as you see i could get the hour only, but other two are confused :s

I know i need to get again the index where is first : until other : and so i could get minute but his was confused for me and i got only errors converting "4:",":",":1"

Answer 1

This will do it:

declare @time varchar(32) = '29:14:2'

select hh = convert(int,
              left( @time ,
                charindex(':',@time)
                - 1
              )
            ) ,
       mm = convert(int,
              left(            right(@time,len(@time)-charindex(':',@time))   ,
                charindex(':', right(@time,len(@time)-charindex(':',@time)) )
                - 1
              )
            ) ,
       ss = convert(int ,
              right(               right(@time,len(@time)-charindex(':',@time) )   ,
                len(               right(@time,len(@time)-charindex(':',@time) ) )
                - charindex( ':' , right(@time,len(@time)-charindex(':',@time) ) )
              )
            )

And it will almost certainly break if — when — your data isn't clean.