By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Time Over 24 Hours

P: n/a
I am creating a report that has call duration and if i run it over a
multi-day span the duration is limited to 24 hours and starts over at
zero. is there a way to not limit hours?

I am usign the following in my select statement:

convert(varchar(12), dateadd(s,
SUM(ISNULL(Calls.OutgoingCallDuration,0)), '19900101'), 108) AS
[Outgoing Call Duration]

Apr 18 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a

Rather than using CONVERT, you can get the number
of hours, minutes and seconds using this

Hours = OutgoingCallDuration / 3600
Mins = (OutgoingCallDuration % 3600) / 60
Seconds = OutgoingCallDuration % 60

Your statement will end up like this

RIGHT('00'+CAST((SUM(ISNULL(Calls.OutgoingCallDura tion,0))/3600) AS
VARCHAR(2)),2) +
RIGHT('00'+CAST(((SUM(ISNULL(Calls.OutgoingCallDur ation,0)) % 3600) /
60) AS VARCHAR(2)),2) +
RIGHT('00'+CAST((SUM(ISNULL(Calls.OutgoingCallDura tion,0)) % 60) AS
VARCHAR(2)),2)

You may find it more efficient to do this in your front end

Apr 18 '06 #2

P: n/a
> I am creating a report that has call duration and if i run it over a
multi-day span the duration is limited to 24 hours and starts over at
zero. is there a way to not limit hours?

I am usign the following in my select statement:

convert(varchar(12), dateadd(s,
SUM(ISNULL(Calls.OutgoingCallDuration,0)), '19900101'), 108) AS
[Outgoing Call Duration]

There _is_ no time over 24 hours.

Think of the problem differently: you're trying to put a _duration_ into
a place that only allows _time_ values. That's not going to fit. Well,
yeah, it fits if the duration is below the length of 24 hours, but that's
it.

Now, given that, a "duration" could never be the same as a "time"
(eg: 01:23 PM).
--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
Apr 18 '06 #3

P: n/a
Events need to be modeled as start and finish timestamps. You can then
easily compute the number of minutes or whatever unit you wish from
those two columns.

Apr 18 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.