Getdate() inside a Function

[chandru_244]

Hi ,
Follwoing is a simple function to retreive a date basis a criteria...
Logic :
If Date is not null in table A return Date from Table A
else retrun Getdate().
Following the function

Create Function RetreiveDate()
Returns DateTime
As
Begin
Declare @ReqdDate DateTime

Select @ReqdDate = Operating_Date From TableA
If @ReqdDate Is Null
Begin
Set @ReqdDate = getdate()
End
Return @ReqdDate
End

When i try to compile the above function it throws the following error
Server: Msg 443, Level 16, State 1, Procedure Sp_Acc_GetOperatingDate, Line 10
Invalid use of 'getdate' within a function.

Y is this happening..
is there any way to use Getdate inside a function

Tx in advance

Rgds,
Chandru

[hspc]

Hi
I assume that you use MS SQL Server:
Use of nondeterministic functions inside user defined functions is not allowd in SQL Server 2000.

Built-in nondeterministic functions are not allowed in the body of user-defined functions.
SQL Server 2000 built-in functions are either deterministic or nondeterministic. Functions are deterministic when they always return the same result any time they are called with a specific set of input values. Functions are nondeterministic when they could return different results each time they are called, even with the same specific set of input values.

You can rewrite the required functionality as a sub query with CASE .. When .. End