GETDATE inside a User Function??? 1

[rmahawaii]

Hello, I am getting a 'Invalid use of 'getdate' within a function.' error when I use this GETDATE() function in my user function. Is there a way to work around this? thanks in advance.

Create Function tb_getCustID ()

Returns @getCustIDTb TABLE
(
[ChangeType] [char] (1) ,
[BeforeID] [char] (16) ,
[AfterID] [char] (16) ,
[BeforeIDType] [char] (2) ,
[AfterIDType] [char] (2)
)

AS

Begin

declare @todayDate Date
set @todayDate = GETDATE() <---------ERROR Raised
.....

 

[NathanGriffiths]

Hi,

GetDate() cannot be used in a User Defined Function as it is non-deterministic i.e. the function would produce a different result every time it was called from the same set of input values.

To get around this, perhaps you could create a DEFAULT using GetDate() on whatever column you intend inserting the variable @todayDate, instead of setting the date within the function. Then the date would be generated on INSERT.

Hope that helps,

Nathan

 

[rmahawaii]

Thanks for your reply, but I need to compare a column's date with today's inside the function. What other ways I can get around this error? Thanks.

 

[JamesLean]

Instead of declaring the variable @todayDate within your function code, make it an input parameter. Then when you call the function, pass in getdate() as the parameter. --James