
June 24th, 2004, 01:24 PM
|
|
Registered User
|
|
Join Date: Jun 2004
Posts: 1
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
|
Time resets after 24 hours
Greetings,
I have a small problem. I need to extract data from a telephone system that has a built in SQL database, unfortunately I can't chagne any data types or how the data is stored. My query needs to calculate the amount of time a rep was on the phone (duration) I would like a total of this data as well (adding over months, etc.). Problem is, the time is stored in number of seconds. I would like to convert this to a time value, but every time I do it resets the hours after the 24th hour, (ex. rep on the phone for 29 hours, it will say 05:00:00, instead of 29:00:00). I'm a MySQL/Oracle programmer by nature, and I'm not familiar with SQL server's datatypes, can anyone provide any help on the query below to help with this.
SELECT distinct datename(q, date_time) as Quarter,
Convert(varchar(8), DateAdd(s, sum(duration),
DateAdd(day, DateDiff(day, 0, GetDate()), 0)), 108) AS 'Duration'
FROM MICROTEL.MCW_Calls
WHERE extension = '1102'
AND datename(q, date_time) = '2'
GROUP BY datename(q, date_time)
ORDER BY datename(q, date_time)
Thanks in advance,
Dominic
|