SQL Timestamp help - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Timestamp help

Author

Topic

Acorsys
Starting Member

2 Posts

Posted - 2004-06-24 : 13:20:45

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

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-24 : 13:47:30

There is not datetime value of 29 hours, you could store the start and end datetime of the call or you could store the time as nvarchar... or leave it as is.

This might help:

Declare @seconds int

Set @seconds = 115124

Select
Duration = right('00' + convert(nvarchar,hours),2) + ':' + right('00' + convert(nvarchar,minutes),2) + ':' + right('00' + convert(nvarchar,seconds),2)
From
(Select
hours = @seconds/3600,
minutes = (@seconds%3600)/60,
seconds = @seconds%3600%60
) as A

Corey

Acorsys
Starting Member

2 Posts

Posted - 2004-06-24 : 14:44:05

That worked great. Thanks.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-24 : 15:50:17

by the way that whole deal can also be done in a function or in one line:

Declare @seconds int

Set @seconds = 115124

Select
Duration = right('00' + convert(nvarchar,@seconds/3600),2) + ':' + right('00' + convert(nvarchar,(@seconds%3600)/60),2) + ':' + right('00' + convert(nvarchar,@seconds%3600%60),2)

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources