adding seconds up from varchar field??? 1

[goland]

Hi all,
Anyone know of a good way to add seconds up from a field that displays in text like the following:
01:51
00:24
01:19
34:32
The file comes as a test file and col009 is a varchar.
I need to add up the total seconds for a record in these files.
I have tried using datepart but keep getting this error:

(459 row(s) affected)
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
Any Ideas would be greatly appreciated,
wyles

 

[maswien]

what's this column stands for? why you need to get the second part and sum them up?

 

[goland]

It is a phone call length field and I need to add it up for billing purposes. I need it in seconds for one of our customers.
THX,
wyles

 

[maswien]

Try this:

select sum(datediff(ss, '00:00', time_field) + 0.0) from myTable

 

[goland]

I still get:
Arithmetic overflow error converting expression to data type datetime.

I tried this earlier and it worked on a small sampple file but not on the real file which contains 50000 rec or so.

select [col009],DATEPART(hour,[col009])* 60 +
DATEPART(Minute,[col009])ACTUAL_SECONDS
from apr_primus

 

[ESquared]

That's not going to work. The times are seen as hours and minutes, not minutes and seconds. You'd have to do

select sum(datediff(ss, 0, [blue]'00:'[/blue] + time_field)) from myTable

And it sucks that you can't use datetime values in a sum aggregation because it would be more efficient to add them all first, then do the datediff once. On a really huge set of data, it could make a difference. So you'd have to convert to float, first.

And even this modification won't work because there's also the problem if someone has more than 24 hours of talk time... 24:00:01 will not convert to datetime.

So you may as well do:

select Sum(left(TimeCol, charindex(':', TimeCol) - 1) * 60 + substring(TimeCol, charindex(':', TimeCol) + 1, 2))

 

[earthandfire]

ESquared,
If this is call logging system, then surely the date part would also be included. If so would this not allow proper arithmetic after conversion to a float?

By the way, I miss Wordplay - an excellent forum

 

[ESquared]

If the time column is just a text listing of minutes and seconds, or hours minutes and seconds, then it will not be possible to convert all of them to datetime.

One could run the following query to find nonconvertable records

SELECT DISTINCT TimeCol FROM TheTable WHERE NOT IsDate(TimeCol)

That should prove the point well enough.

 

[goland]

ESquared ,Thanks a bunch
That works great.
21 of the columns were not coming back as a valid date for some reason which caused errors using datepart
thnaks alot,
wyles

 

[ESquared]

goland, you should examine all rows which show up in the following query, to ensure the values are valid and are being tabulated correctly. You don't want to have any errors.

SELECT TimeCol FROM TheTable
WHERE
  Right('999' + TimeCol, 7) NOT LIKE '[0-9][0-9][0-9][0-9]:[0-9][0-9]'

This ensures the values are all in the format
NNN#:## where # is a digit from 0-9 and N is missing or is a digit from 0-9. A more straightforward and possibly faster query is:

SELECT TimeCol FROM TheTable
WHERE
  TimeCol NOT LIKE '[0-9][0-9][0-9][0-9]:[0-9][0-9]'
  AND TimeCol NOT LIKE '[0-9][0-9][0-9]:[0-9][0-9]'
  AND TimeCol NOT LIKE '[0-9][0-9]:[0-9][0-9]'
  AND TimeCol NOT LIKE '[0-9]:[0-9][0-9]'

Hey, wait... a different way to think about it that's more flexible:

SELECT TimeCol FROM TheTable
WHERE
  TimeCol LIKE '%[^0-9:]%'
  OR TimeCol NOT LIKE '%[0-9]:[0-9][0-9]'
  OR TimeCol LIKE '%:%:%;

I wish common expressions were built into SQL server! You can add them with some VB calls, but it's a pain.

 

[maswien]

Good point Esquared! didn't think that much. worth a star.