my query and user defined fuction problem

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)

my query and user defined fuction problem

Author

Topic

hong_ma
Starting Member

37 Posts

Posted - 2006-02-22 : 13:56:56

I have a table which has six fields ID, dateDue, dateReceived, dueday. month, ContactFYE
my query looks like

select ID, DateDue, dateReceived, dueday, month, ContactFYE
from Report
where (dbo.Report.DateDue BETWEEN dbo.udfDisplayTime(dueday, month, ContactFYE) AND DateDue )

user defined function

CREATE FUNCTION dbo.udfDisplayTime ( @dueday int, @month int, @ContactFYE smalldatetime)
RETURNS smalldatetime AS
BEGIN
DECLARE @ReturnString AS smalldatetime
Declare @dueday1 as int
if (@month =1)
begin
set @dueday1 = @dueday -7
end
else if (@month =2)
begin
set @dueday1 = @dueday -14
end
else if (@month =3)
begin
set @dueday1 = @dueday -60
end

select @ReturnString = DATEADD ( dd, @dueday1, @ContactFYE)
Return @ReturnString

END

I got a incorrect result when using this query and user defined function.
the result that I got was out of between displaytime and datdue.

any idea for this , Thanks.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-22 : 14:09:36

Hints:
1. Execute the function alone with necessary parameters and see whether it returns the date that you expect
2. Is DateDue also of smalldatetime ? (I don't know whether that matters but you can check
3. Hardcode the "expected results of function" to the query and execute

hong_ma
Starting Member

37 Posts

Posted - 2006-02-22 : 15:25:07

I am not sure if the problem comes from where condition
where (dbo.Report.DateDue BETWEEN dbo.udfDisplayTime(dueday, month, ContactFYE) AND DateDue )

becasue udfDisplayTime always changed, not a fixed value

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources