DateTime has no time field on SP

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

SQL Server Development (2000)

DateTime has no time field on SP

Author

Topic

sz1
Aged Yak Warrior

555 Posts

Posted - 2010-10-27 : 07:10:42

Hello

I have a SP that allows the user to enter date parameters with time, this works ok in Crystal reports but when its run from reporting services there is no time field, you can type it in but there is no 00:00:00 just the time format...any idea how to get the 00:00:00 in there?

Thanks

Params
@Start DATETIME,
@End DATETIME

SELECT Agent,
CONVERT(CHAR(8), DATEADD(SECOND, (AVG(CallCompletedAfter - CallAnsweredAfter)), 0), 8) AS 'Average Duration'
--AVG(CallCompletedAfter - CallAnsweredAfter)AS 'Average Duration'
FROM CallSummary
WHERE (CallOffered >= @Start) AND (CallOffered <= @End)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-27 : 08:00:58

Sorry but it is not clear.
Do you have a problem in T-SQL or do you have a problem in Reporting Services (Version?)?

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-27 : 08:27:50

[code]SELECT Agent,
CONVERT(CHAR(8), DATEADD(MILLISECOND, AVG(DATEDIFF(MILLISECOND, CallAnsweredAfter, CallCompletedAfter)), 0), 8) AS [Average Duration]
FROM dbo.CallSummary
WHERE CallOffered >= @Start
AND CallOffered <= @End
GROUP BY Agent[/code]

N 56°04'39.26"
E 12°55'05.63"

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-27 : 09:14:46

quote:

Originally posted by Peso

SELECT		Agent, 
		CONVERT(CHAR(8), DATEADD(MILLISECOND, AVG(DATEDIFF(MILLISECOND, CallAnsweredAfter, CallCompletedAfter)), 0), 8) AS [Average Duration]
FROM		dbo.CallSummary
WHERE		CallOffered >= @Start
		AND CallOffered <= @End
GROUP BY	Agent

N 56°04'39.26"
E 12°55'05.63"

I have never come accross anybody who plays with time calculation like you do.

sz1
Aged Yak Warrior

555 Posts

Posted - 2010-10-27 : 10:30:41

Does not do anything...Milliseconds messed Crystal report up so changed this back to seconds but removed ().

I'm creating the report also with business intelligence studio - visual studio, maybe its seomthing to do with how that sees it...as Crystal reports sees the 00:00:00 part ok.
lke I said if you type the time in it works but for the end user they need to see they can change this otherwise no time selection would mean no records...
Thanks

sz1
Aged Yak Warrior

555 Posts

Posted - 2010-10-27 : 10:55:21

Actuall missed the datediff part oops...getting now
The datediff function requires 3 argument(s).

I'll post the full thing...

thanks for ya help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources