Monday, February 20, 2012

my query and user defined fuction problem

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.I think we are going to need to see some data and desired results in order to be able to help you. The purpose of the function and your different data columns is not obvious (to me at least).|||

The query you have is the same as:

select ID, DateDue, dateReceived, dueday, month, ContactFYE
from Report

the where condition you have specified will always be true, because DateDue is ALWAYS between (anything) and DateDue. It makes no difference what the udf returns.

No comments:

Post a Comment