converting time string to total seconds

[GregVM]

I have an entry in a table for time duration that is formatted as "xxx minutes xx seconds". The application never shows "hours", just "xxx minutes xx seconds" and the minutes can climb as high as it gets (some entries have over 2000 minutes!). Anyways, I'd like to convert this string to total seconds in numeric datatype so that I can run SUM on the table for user stats. Any scripts that will make this easy?

TIA

 

[tlbroadbent]

Try this.

Select NmbrSeconds=
Cast(Left(Duration,Charindex('minutes',Duration)-1) As Int) * 60 +
Cast(Substring(Duration,Charindex('minutes',Duration)+8,2) As Int) Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[GregVM]

thanks Terry. that looks like it's on the right path, however I'm receiving "Error converting data type varchar to numeric." errors from Query Analyzer. I'm trying to determine if the data is some how not returning numeric values now.
-Greg

 

[tlbroadbent]

The code is simplistic. It depends on the format of the character string being exactly as you posted. If there are variations, you'll have o modify the code to handle them. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[GregVM]

understood ... what I forgot to mention is that some entries contain only "xx seconds" if that's all the time the user was connected. To catch these I added a CASE statement, and that's probably where it's messing-up. Here's a mock layout I've been working with:

1)
--declare string variables
declare @mystring varchar(1000)
declare @durstring varchar(1000)
declare @length int
declare @len1 int
declare @len2 int

set @mystring = 'The user DOMAIN\userid connected on port Mdm15 on 08/23/2002 at 07:25am and disconnected on
08/23/2002 at 07:27am. The user was active for 2 minutes 23 seconds. 78809 bytes were sent and 50675 bytes were received. The port speed was 49300.'

2)
--grab the time duration string
set @len1 = charindex('user was active for ',@mystring,1)
set @len2 = charindex('.',@mystring,(@len1))
set @durstring = replace(substring(@mystring,@len1,@len2-@len1),'user was active for ','')

3)
print 'Duration:' + CASE WHEN @durstring LIKE 'minutes' THEN
Cast(Left(@durstring,charindex('minutes',@durstring)-1) AS Numeric) * 60
+ Cast(substring(@durstring,charindex('minutes',@durstring)+8,2) AS Numeric)
ELSE
-- no minutes in string ... just seconds
Cast(substring(@durstring,charindex('seconds',@durstring)+8,2) AS Numeric)
END

I omitted the code that extracts several other values (port speed, date, time etc), but those are working fine. This is the one that causes issues right now.

thanks again Terry

 

[tlbroadbent]

The final Print statement requires the following changes.

[li] Need wild cards for LIKE to find minutes. You could also use charindex('minutes', @durstring)>0.
[li] Need to subtract 1 from position of seconds rather than add 8.
[li] Need to convert the numeric result to a character string in order to concatenate with 'Duration:'

Replace the final Print statement with the following.

print 'Duration:' + str(CASE WHEN @durstring LIKE '%minutes%' THEN
Cast(Left(@durstring,charindex('minutes',@durstring)-1) AS Numeric) * 60
+ Cast(substring(@durstring,charindex('minutes',@durstring)+8,2) AS Numeric)
ELSE
-- no minutes in string ... just seconds
Cast(substring(@durstring,charindex('seconds',@durstring)-1,2) AS Numeric)
END) Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[GregVM]

excellent Terry! SQL is definitely not one of my strengths as you can tell, but I'm working on it.

thanks again for your help
-Greg