We have a table that is getting written to by a third party app. It is a varchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' /> and is used to keep time (hh:mm<img src='/community/emoticons/emotion-7.gif' alt=':s' />s or 04:32:42). We would like to sum this column. Is there any easy way to do this? I'm using the following query right now, but it's clunky, and doesn't give the correct info in the case where one of the values sums to more then 59 (i can get 04:32:67, which should be 04:33:07).<br /><br />select tag,(right('0' + cast(sum(cast(SUBSTRING(deltatime,1,2) as int)) as varchar),2) +':'+ right('0' + cast(sum(cast(SUBSTRING(deltatime,4,2) as int)) as varchar),2) +':'+ right('0' + cast(sum(cast(SUBSTRING(deltatime,7,2) as int)) as varchar),2)) as time<br />from alarmlog<br />group by tag<br /><br />Thanks in advance,<br /><br />Ben
this will give you in days - hours - minutes - seconds selectright(convert(varchar(20), dateadd(second, sum(datediff(second, 0, convert(datetime, col))), 0), 120), 11) from ( select'04:32:42' as colunion all select'22:33:44' as colunion all select'12:34:56' as col ) a KH
this in detail breakdown of the steps selectright(convert(varchar(20), dte, 120), 11) from ( selectdte = dateadd(second, tot_secs, 0) from ( selecttot_secs = sum(secs) from ( selectsecs = datediff(second, 0, dt) from ( selectdt = convert(datetime, col) from ( select'04:32:42' as colunion all select'22:33:44' as colunion all select'12:34:56' as col ) a ) b ) c ) d ) e KH
That's awesome! Thank you. I'm assuming that the three selects at the end are just to test the query. When I tried replacing them with my column, i got the error: Syntax error converting datetime from character string. Here is my query:<br /><br />selectright(convert(varchar(20), dateadd(second, sum(datediff(second, 0, convert(datetime, col))), 0), 120), 11)<br />from<br />(<br />selectdeltatime col from alarmlog//deltatime is a varchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' /> column<br />) a<br /><br />Can you let me know what I can do to fix this? Thanks,<br /><br />Ben
