GETDATE() in a UDF

[MarkLappin]

Howdy,

I am attempting to write a UDF which I can use to check to see if a member is currently active in our organization; right now this is a simple datediff by month but the definition of an active member may and will change so the logic to calculate and active member I want to be centrally stored and then use the function to calculate if the member is or is not active so that logic is stored on one placed.

So there are several different ways which this needs to happen right now and thats is we want to count how many members expired in month previously, the current one, or some future one, so I've written a UDF which I'm attempting to use and will be pasted below. All I want this to do is return a bit value indicating if the member is active. Here is the trouble I am having:
IF the function is not passed a date, it uses GETDATE() otherwise it uses the date passed in as an argument. In the parameter list, GETDATE() will not "compile" BUT GETDATE will but does not work.

The full UDF is below and any suggestions/solutiosn will be much appreciated!

I'm developing on SQL 2000 Personal Edition off an Enterprise Install CD -- there are two target environments, one is a SQL 2000 Standard SP4 on 2003 SP1 server, and the other is a SQL Enterprise 2000 SP4 shared hosting environment.

--Mark

CREATE FUNCTION [dbo].[mbr_isActive] (@memberNumber int, @expDateCheck dateTime = GETDATE)
RETURNS bit AS
BEGIN
DECLARE @isActive as bit
SELECT @isActive = (SELECT 0)
DECLARE @expDate as datetime
SELECT @expDate = (SELECT dateExpire from mbrMembership WHERE memberNumber = @memberNumber)
DECLARE @diffAmt as int
SELECT @diffAmt = (SELECT DATEDIFF(mm, @expDateCheck, @expDate))
SELECT @isActive = (SELECT CASE
WHEN @diffAmt >= 0 THEN 1
WHEN @diffAmt < 0 THEN 0
END)
RETURN @isActive
END

 

[amrita418]

The syntax to return the current date is getdate().

I have made some minor changes to your code, let me know if it works. Also, you might want to add some error checks.

CREATE FUNCTION [dbo].[mbr_isActive] (@memberNumber int, @expDateCheck dateTime = GETDATE())  
RETURNS bit AS  
BEGIN 
    DECLARE     @isActive bit,
		@expDate datetime,
		@diffAmt int

    SET 	@expDate = (SELECT dateExpire from mbrMembership WHERE memberNumber = @memberNumber)
    SET		@isActive = (SELECT CASE WHEN DATEDIFF(mm, @expDateCheck, @expDate) >= 0  THEN  1 ELSE 0  END)    
    RETURN      @isActive
END

PS: Code not tested.

Regards,
AA

 

[RobertT687]

Check Books on Line. You cannot use GetDate() in a UDF because it is a non-deterministic function.

 

[MarkLappin]

RobertT687 -- yeup, I have discoverd this, thats why I tried to move GETDATE() to be a default argument rather then the function body (and that actually works out better there if I can make it work) --- do you know any work around for getting past that limitation?

I can always just pass GETDATE() as an argument, but thats kind of a pain especially if somebody else goes to use the system other then me later on.


amrita418: I'll give your suggestiosn a shot, I usually go back and add error checks and clean things up after I get things working, what was posted was just trying to get the thing returning output.

Thank ya'll for your suggestions!

 

[amrita418]

Good point Robert! I do not how I overlooked that.

Mark,
Here is one workaround to using GetDate() in a UDF. Let me know if it works for you.

CREATE VIEW CurrentDate AS SELECT Date = GETDATE()
GO

CREATE FUNCTION [dbo].[mbr_isActive] (@memberNumber int)
RETURNS bit AS

BEGIN
DECLARE @isActive bit,
@expDate datetime,
@expDateCheck datetime

SELECT @expDateCheck = Date FROM CurrentDate

SET @expDate = (SELECT dateExpire from mbrMembership WHERE memberNumber = @memberNumber)
SET @isActive = (SELECT CASE WHEN DATEDIFF(mm, @expDateCheck, @expDate) >= 0 THEN 1 ELSE 0 END)
RETURN @isActive
END


Regards,
AA