Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ConfusedAgain
Yak Posting Veteran
82 Posts |
 Posted - 2011-04-25 : 07:22:57
|
| I have a field that I want to split up.The field is Calllength and has data that shows a length of time, express as hh:mm:ss or maybe hhh:mm:ss such as:107:45:40 12:30:01I am trying to covert this so it shows the total mins.I was orginally using:SELECT Calllength, SUBSTRING(Calllength, 1, 3) * 60 AS HR, SUBSTRING(Calllength, 5, 2) AS MN, CASE WHEN SUBSTRING(Calllength, 8, 2) > 0 THEN 1 ELSE 0 END AS SSFROM MyTableThis worked fine until I had the 107:45:40. So what I thought I would work from the left and I could select 3 characters 6 charcters from the left start.Hope that makes sense! |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2011-04-25 : 07:35:02
|
| DECLARE @Calllength AS VARCHAR(50)SET @Calllength ='107:45:40'SELECT @Calllength, SUBSTRING(@Calllength, 1, CHARINDEX(':',@Calllength)-1) * 60 AS HR, SUBSTRING(@Calllength, CHARINDEX(':',@Calllength)+1, 2 )AS MN, REVERSE(SUBSTRING(REVERSE(@Calllength),1, CHARINDEX(':',REVERSE(@Calllength))-1) )AS SS--------------------------http://connectsql.blogspot.com/ |
 |
|
|
ConfusedAgain
Yak Posting Veteran
82 Posts |
Posted - 2011-04-25 : 07:45:01
|
| Nice one.Many thanks. |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2011-04-25 : 07:52:46
|
| You're welcome, glad to help.--------------------------http://connectsql.blogspot.com/ |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-04-25 : 07:54:18
|
| declare @str varchar(20)set @str = '107:45:40'-- '12:30:01' select parsename(replace(@str,':','.'),3)*60 + parsename(replace(@str,':','.'),2)JimEveryday I learn something that somebody else already knew |
|
|
|
|
|
|
|
|
Selecting mid characters from the left